diff --git a/library/rating.php b/library/rating.php
index d583dcc..79d381f 100644
--- a/library/rating.php
+++ b/library/rating.php
@@ -1,9595 +1,9595 @@
db = $db;
$this->settings = $settings;
if ($this->database_backend == "mysql") {
$this->db->Halt_On_Error="no";
}
if ($this->settings['priceDenominator']) {
$this->priceDenominator = $this->settings['priceDenominator'];
}
if ($this->settings['priceDecimalDigits']) {
$this->priceDecimalDigits = $this->settings['priceDecimalDigits'];
}
if ($this->settings['durationPeriodRated']) {
$this->durationPeriodRated = $this->settings['durationPeriodRated'];
}
if ($this->settings['trafficSizeRated']) {
$this->trafficSizeRated = $this->settings['trafficSizeRated'];
}
if ($this->settings['rate_longer_than']) {
// if call is shorter than this, it has zero cost
$this->rate_longer_than = $this->settings['rate_longer_than'];
}
if ($this->settings['min_duration']) {
// if call is shorter than this, it has zero cost
$this->min_duration = $this->settings['min_duration'];
}
if ($this->settings['increment']) {
$this->increment = $this->settings['increment'];
}
if ($this->settings['database_backend']) {
$this->database_backend = $this->settings['database_backend'];
}
}
public function calculateAudio($dictionary)
{
// used for calculate rate for audio application
$this->RatingTables = $dictionary['RatingTables'];
$this->callId = $dictionary['callId'];
$this->timestamp = $dictionary['timestamp'];
$this->duration = $dictionary['duration'];
$this->traffic = 2 * ($dictionary['inputTraffic'] + $dictionary['outputTraffic']);
$this->DestinationId = $dictionary['DestinationId'];
$this->BillingPartyId = $dictionary['BillingPartyId'];
$this->domain = $dictionary['domain'];
$this->gateway = $dictionary['gateway'];
$this->ResellerId = $dictionary['ResellerId'];
$this->aNumber = $dictionary['aNumber'];
$this->cNumber = $dictionary['cNumber'];
$this->ENUMtld = $dictionary['ENUMtld'];
if ($this->rate_longer_than && $this->duration < $this->rate_longer_than) {
//syslog(LOG_NOTICE, "Duration less than minimum $this->rate_longer_than");
$this->rateInfo .= " Duration < $this->rate_longer_than s\n";
return true;
}
if ($this->ENUMtld && $this->ENUMtld != 'n/a' && $this->ENUMtld != 'none' && $this->RatingTables->ENUMtlds[$this->ENUMtld]) {
$this->ENUMdiscount = $this->RatingTables->ENUMtlds[$this->ENUMtld]['discount'];
if (!is_numeric($this->ENUMdiscount) || $this->ENUMdiscount < 0 || $this->ENUMdiscount > 100) {
syslog(LOG_NOTICE, "Error: ENUM discount for tld $this->ENUMtld must be between 0 and 100");
}
}
if (!$this->gateway) {
$this->gateway = "0.0.0.0";
}
if (!$this->duration) {
$this->duration = 0;
}
if (!$this->traffic) {
$this->traffic = 0;
}
$this->application=$dictionary['application'];
if (!$this->application) {
$this->application = 'audio';
}
$durationRate = 0;
$foundRates = array();
if (!$this->DestinationId) {
syslog(LOG_NOTICE, "Error: Cannot calculate rate without destination id for callid=$this->callId");
return false;
}
if (!$this->lookupDestinationDetails()) {
// get region, increment and other per destination details
syslog(LOG_NOTICE, "Error: Cannot find destination details for call_id=$this->callId, dest_id=$this->DestinationId)");
return false;
}
if (!$this->lookupProfiles()) {
// get profiles for the billing party
syslog(LOG_NOTICE, "Error: Cannot find any profiles for call_id=$this->callId, dest_id=$this->DestinationId)");
return false;
}
// lookup discounts if any
$this->lookupDiscounts();
$this->startTimeBilling = getLocalTime($this->billingTimezone, $this->timestamp);
list($dateText,$timeText) = explode(" ", trim($this->startTimeBilling));
$Bdate = explode("-", $dateText);
$Btime = explode(":", $timeText);
$this->timestampBilling = mktime($Btime[0], $Btime[1], $Btime[2], $Bdate[1], $Bdate[2], $Bdate[0]);
$this->startTimeBilling = Date("Y-m-d H:i:s", $this->timestampBilling);
$this->trafficKB = number_format($this->traffic/1024, 0, "", "");
// check min_duration and increment per destination
if ($this->increment >= 1) {
// increase the billed duration to the next increment
$this->duration = $this->increment * ceil($this->duration / $this->increment);
}
if ($this->max_duration && $this->duration > $this->max_duration) {
// limit the maximum duration for rating
$this->duration = $this->max_duration;
}
$this->rateSyslog = "";
if ($this->duration) {
if ($this->increment >= 1) {
$this->rateInfo .=
" Increment: $this->increment s\n";
$this->rateSyslog .= sprintf("Increment=%s ", $this->increment);
}
if ($this->min_duration) {
$this->rateInfo .=
" Min duration: $this->min_duration s\n";
$this->rateSyslog .= sprintf("MinDuration=%s ", $this->min_duration);
}
if ($this->max_duration) {
$this->rateInfo .=
" Max duration: $this->max_duration s\n";
$this->rateSyslog .= sprintf("MaxDuration=%s ", $this->max_duration);
}
if ($this->max_price) {
$this->rateInfo .=
" Max price: $this->max_price\n";
$this->rateSyslog .= sprintf("MaxPrice=%s ", $this->max_price);
}
unset($IntervalsForPricing);
$this->rateInfo .=
" Duration: $this->duration s\n".
" App: $this->application\n".
" Destination: $this->DestinationId\n".
" Customer: $this->CustomerProfile\n";
if ($this->region) {
$this->rateInfo .=
" Region: $this->region\n";
}
if ($this->discount_duration || $this->discount_connect) {
$this->rateInfo .=
" Discount: ";
}
if ($this->discount_connect) {
$this->rateInfo .= " connect $this->discount_connect% ";
}
if ($this->discount_duration) {
$this->rateInfo .= " duration $this->discount_duration% ";
}
if ($this->discount_duration || $this->discount_connect) {
$this->rateInfo .= "\n";
}
if ($this->ENUMtld && $this->ENUMtld != 'none' && $this->ENUMtld != 'n/a') {
$this->rateInfo .=
" ENUM tld: $this->ENUMtld\n".
" ENUM discount: $this->ENUMdiscount%\n";
}
$i=0;
$durationRatedTotal=0;
// get recursively a set of arrays with rates
// until we billed the whole duration
while ($durationRatedTotal < $this->duration) {
if ($i == "0") {
$dayofweek = date("w", $this->timestampBilling);
$hourofday = date("G", $this->timestampBilling);
$dayofyear = date("Y-m-d", $this->timestampBilling);
} else {
$dayofweek = date("w", $this->timestampBilling+$durationRatedTotal);
$hourofday = $foundRate['nextHourOfDay'];
$dayofyear = date("Y-m-d", $this->timestampBilling+$durationRatedTotal);
}
$foundRate = $this->lookupRateAudio($dayofyear, $dayofweek, $hourofday, $durationRatedTotal);
$durationRatedTotal = $durationRatedTotal + $foundRate['duration'];
if (!$foundRate['rate']) {
$this->broken_rate=true;
return false;
}
$foundRates[] = $foundRate;
$i++;
if ($i > 10) {
// possible loop because of wrong coding make sure we end this loop somehow
$body="Rating of call $this->callId (DestId=$this->DestinationId) has more than 10 spans. It could be a serious bug.\n";
mail($this->toEmail, "CDRTool rating problem", $body, $this->extraHeaders);
syslog(LOG_NOTICE, "Error: Rating of call $this->callId (DestId=$this->DestinationId) has more than 10 spans.");
break;
}
}
}
$j = 0;
$span = 0;
foreach ($foundRates as $thisRate) {
$spanPrice = 0;
$span++;
if ($j > 0) {
$payConnect = 0;
$durationForRating = $thisRate['duration'];
} else {
$payConnect=1;
if ($this->min_duration && $this->duration < $this->min_duration) {
$durationForRating = $this->min_duration;
} else {
$durationForRating = $thisRate['duration'];
}
}
$connectCost = $thisRate['values']['connectCost'];
$durationRate = $thisRate['values']['durationRate'];
// apply discounts for connect
if ($this->discount_connect) {
$connectCost = $connectCost - $connectCost * $this->discount_connect / 100;
}
// apply discounts for duration
if ($this->discount_duration) {
$durationRate = $durationRate - $durationRate * $this->discount_duration / 100;
}
$connectCostIn = $thisRate['values']['connectCostIn'];
$durationRateIn = $thisRate['values']['durationRateIn'];
if ($span=="1") {
$connectCostSpan = $connectCost;
$this->connectCost = number_format($connectCost/$this->priceDenominator, $this->priceDecimalDigits);
$connectCostSpanIn = $connectCostIn;
$this->connectCostIn = number_format($connectCostIn/$this->priceDenominator, $this->priceDecimalDigits);
} else {
$connectCostSpan=0;
$connectCostSpanIn=0;
}
$connectCostPrint = number_format($connectCostSpan/$this->priceDenominator, $this->priceDecimalDigits);
$durationRatePrint = number_format($durationRate/$this->priceDenominator, $this->priceDecimalDigits);
$connectCostPrintIn = number_format($connectCostSpanIn/$this->priceDenominator, $this->priceDecimalDigits);
$durationRatePrintIn = number_format($durationRateIn/$this->priceDenominator, $this->priceDecimalDigits);
if (!$connectCostSpan) $connectCostSpan=0;
if (!$durationRate) $durationRate=0;
if (!$connectCostSpanIn) $connectCostSpanIn=0;
if (!$durationRateIn) $durationRateIn=0;
if (!$this->inputTraffic) $this->inputTraffic=0;
if (!$this->outputTraffic) $this->outputTraffic=0;
if ($span>1) $this->rateInfo .= "--\n";
/*
durationRate*durationForRating/durationPeriodRated/priceDenominator+
trafficRate/priceDenominator/trafficSizeRated*(inputTraffic+outputTraffic)/8");
$durationRate*$durationForRating/$this->durationPeriodRated/$this->priceDenominator+
$trafficRate/$this->priceDenominator/$this->trafficSizeRated*($this->inputTraffic+$this->outputTraffic)/8");
*/
$spanPrice = $durationRate * $durationForRating / $this->durationPeriodRated / $this->priceDenominator;
$this->price = $this->price+$spanPrice;
$spanPricePrint = number_format($spanPrice, $this->priceDecimalDigits);
$spanPriceIn = $durationRateIn * $durationForRating / $this->durationPeriodRated / $this->priceDenominator;
$this->priceIn = $this->priceIn+$spanPriceIn;
$spanPricePrintIn = number_format($spanPriceIn, $this->priceDecimalDigits);
if ($span=="1" && $thisRate['profile']) {
if ($connectCostIn) {
$this->rateInfo .=
" Connect in: $connectCostPrintIn\n";
}
$this->rateInfo .=
" Connect: $connectCostPrint\n".
" StartTime: $this->startTimeBilling\n".
"--\n";
$this->rateSyslog .= "ConnectFee=$connectCostPrint ";
$this->price = $this->price + $connectCostSpan / $this->priceDenominator * $payConnect;
$this->priceIn = $this->priceIn + $connectCostSpanIn / $this->priceDenominator * $payConnect;
}
$this->rateInfo .=
" Span: $span\n".
" Duration: $durationForRating s\n";
$this->rateSyslog .= sprintf(
"CallId=%s Span=%s Duration=%s DestId=%s %s",
$this->callId,
$span,
$durationForRating,
$this->DestinationId,
$thisRate['customer']
);
if ($thisRate['profile']) {
$this->rateInfo .=
" ProfileId: $thisRate[profile] / $thisRate[day]\n".
" RateId: $thisRate[rate] / $thisRate[interval]h\n".
" Rate: $durationRatePrint / $this->durationPeriodRated s\n".
" Price: $spanPricePrint\n";
if ($spanPriceIn) {
$this->rateInfo .=
" Price in: $spanPricePrintIn\n";
}
$this->rateSyslog .= sprintf(
" Profile=%s Period=%s Rate=%s Interval=%s Cost=%s/%s",
$thisRate['profile'],
$thisRate['day'],
$thisRate['rate'],
$thisRate['interval'],
$durationRatePrint,
$this->durationPeriodRated
);
} else {
$this->rateInfo .=
" ProfileId: none\n".
" RateId: none\n";
$this->rateSyslog .= " Profile=none, Rate=none";
}
$this->rateSyslog .= " Price=".sprintf("%.4f", $spanPrice);
$this->rateSyslog .= " PriceIn=".sprintf("%.4f", $spanPriceIn);
if ($this->discount_connect) {
$this->rateSyslog .= sprintf(" DisCon=%s", $this->discount_connect);
}
if ($this->discount_duration) {
$this->rateSyslog .= sprintf(" DisDur=%s", $this->discount_duration);
}
syslog(LOG_NOTICE, $this->rateSyslog);
$j++;
}
if ($this->priceIn) {
$this->rateInfo .= "--\n".
" Price out: ".sprintf("%.4f", $this->price)."\n".
" Price in: ".sprintf("%.4f", $this->priceIn)."\n".
" Margin: ".sprintf("%.4f", $this->price-$this->priceIn)."\n";
}
$this->rateInfo=trim($this->rateInfo);
if ($this->max_price && $this->price > $this->max_price) {
$this->price = $this->max_price;
}
if ($this->ENUMdiscount) {
$this->priceBeforeDiscount = sprintf("%.4f", $this->price);
$this->price = $this->price - $this->price * $this->ENUMdiscount / 100;
$this->price = sprintf("%.4f", $this->price);
$this->rateInfo .=
"\n--\n".
" Total: $this->priceBeforeDiscount\n".
" Total after discount: $this->price\n";
}
$this->price = sprintf("%.4f", $this->price);
$this->pricePrint = number_format($this->price, $this->priceDecimalDigits);
return true;
}
public function calculateMessage($dictionary)
{
// used for calculate rate for SMS application
$this->RatingTables = $dictionary['RatingTables'];
$this->callId = $dictionary['callId'];
$this->timestamp = $dictionary['timestamp'];
$this->DestinationId = $dictionary['DestinationId'];
$this->BillingPartyId = $dictionary['BillingPartyId'];
$this->domain = $dictionary['domain'];
$this->gateway = $dictionary['gateway'];
$this->ResellerId = $dictionary['ResellerId'];
$this->aNumber = $dictionary['aNumber'];
$this->cNumber = $dictionary['cNumber'];
if (!$this->gateway) {
$this->gateway = "0.0.0.0";
}
$this->application = 'sms';
$foundRates=array();
if (!$this->DestinationId) {
syslog(LOG_NOTICE, "Error calculateMessage(): Cannot calculate rate without destination id");
return false;
}
if (!$this->lookupProfiles()) {
// get profiles for the billing party
syslog(LOG_NOTICE, "Error: calculateMessage() Cannot find any profiles for call_id=$this->callId, dest_id=$this->DestinationId)");
return false;
}
// lookup discounts if any
$this->lookupDiscounts();
$this->startTimeBilling = getLocalTime($this->billingTimezone, $this->timestamp);
list($dateText,$timeText) = explode(" ", trim($this->startTimeBilling));
$Bdate = explode("-", $dateText);
$Btime = explode(":", $timeText);
$this->timestampBilling = mktime($Btime[0], $Btime[1], $Btime[2], $Bdate[1], $Bdate[2], $Bdate[0]);
$dayofweek = date("w", $this->timestampBilling);
$hourofday = date("G", $this->timestampBilling);
$dayofyear = date("Y-m-d", $this->timestampBilling);
$this->rateInfo .=
" App: sms\n".
" Destination: $this->DestinationId\n".
" Customer: $this->CustomerProfile\n";
if ($this->region) {
$this->rateInfo .=
" Region: $this->region\n";
}
if ($this->discount_duration || $this->discount_connect) {
$this->rateInfo .=
" Discount: ";
}
if ($this->discount_connect) {
$this->rateInfo .= " connect $this->discount_connect% ";
}
if ($this->discount_duration || $this->discount_connect) {
$this->rateInfo .= "\n";
}
$foundRate = $this->lookupRateMessage($dayofyear, $dayofweek, $hourofday);
if (is_array($foundRate)) {
$this->price = number_format($foundRate['values']['connectCost'] / $this->priceDenominator, $this->priceDecimalDigits);
$this->price = sprintf("%.4f", $this->price);
$this->pricePrint = $this->price;
$this->rateInfo .=
" ProfileId: $foundRate[profile] / $foundRate[day]\n".
" RateId: $foundRate[rate]\n".
" Price: $this->price\n";
return true;
} else {
return false;
}
}
private function lookupDiscounts()
{
// get discounts for customer per region if set otherwise per destination id
if (!$this->CustomerProfile) {
return false;
}
if ($this->region) {
$_field = 'region';
$_value = $this->region;
} else {
$_field = 'destination';
$_value = $this->DestinationId;
}
if ($this->CustomerProfile == 'default') {
$query = sprintf(
"select * from billing_discounts
where subscriber = ''
and domain = ''
and gateway = ''
and application = '%s'
and %s = '%s'
",
addslashes($this->application),
addslashes($_field),
addslashes($_value)
);
} else {
$els = explode("=", $this->CustomerProfile);
$query = sprintf(
"select * from billing_discounts
where %s = '%s'
and application = '%s'
and %s = '%s'
",
addslashes($els[0]),
addslashes($els[1]),
addslashes($this->application),
addslashes($_field),
addslashes($_value)
);
}
// mysql backend
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$this->db->next_record();
if ($this->db->f('connect') > 0 && $this->db->f('connect') <=100) {
$this->discount_connect = $this->db->f('connect');
}
if ($this->db->f('duration') > 0 && $this->db->f('duration') <=100) {
$this->discount_duration = $this->db->f('duration');
}
}
return true;
}
private function lookupDestinationDetails()
{
// get rating related details for the destination id
if (!$this->DestinationId) {
syslog(LOG_NOTICE, "Error: Cannot lookup destination details without a destination id");
return false;
}
// mysql backend
$query = sprintf(
"select * from destinations
where dest_id = '%s'
and (reseller_id = %d or reseller_id = 0) order by reseller_id desc limit 1
",
addslashes($this->DestinationId),
addslashes($this->ResellerId)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$this->db->next_record();
$this->region = $this->db->Record['region'];
$this->max_duration = $this->db->Record['max_duration'];
$this->max_price = $this->db->Record['max_price'];
if ($this->db->Record['increment']) {
$this->increment = $this->db->Record['increment'];
}
if ($this->db->Record['min_duration']) {
$this->min_duration = $this->db->Record['min_duration'];
}
}
return true;
}
private function lookupProfiles()
{
unset($this->allProfiles);
/*
lookup the profile_name in billing_customers in the following order:
subscriber, username@gateway, domain, gateway (based on $dayofweek):
- profile_workday matches days [1-5] (Work-day)
- profile_weekend matches days [6-0] (Week-end)
- week starts with 0 Sunday and ends with 6 Saturday
Alternatively look for profile_workday_alt and profile_weekend_alt
If no rates are found for destination in the profileX,
than lookup rates in profileX_alt
*/
// mysql backend
list($username, $domain) = explode("@", $this->BillingPartyId);
$trusted_peer_account = sprintf("%s@%s", $username, $this->gateway);
$query = sprintf(
"select * from billing_customers
where
(subscriber = '%s' and domain = '' and gateway = '' )
or (subscriber = '%s' and domain = '' and gateway = '' )
or (domain = '%s' and subscriber = '' and gateway = '' )
or (gateway = '%s' and subscriber = '' and domain = '' )
or (subscriber = '' and domain = '' and gateway = '' )
order by subscriber desc, domain desc, gateway desc limit 1
",
addslashes($this->BillingPartyId),
addslashes($trusted_peer_account),
addslashes($this->domain),
addslashes($this->gateway)
);
dprint_sql($query);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$this->db->next_record();
if ($this->db->Record['subscriber']) {
if ($this->db->Record['subscriber'] == $trusted_peer_account) {
$this->CustomerProfile = sprintf("remote_account=%s", $this->db->Record['subscriber']);
} else {
$this->CustomerProfile = sprintf("local_account=%s", $this->db->Record['subscriber']);
}
} elseif ($this->db->Record['domain']) {
$this->CustomerProfile = sprintf("domain=%s", $this->db->Record['domain']);
} elseif ($this->db->Record['gateway']) {
$this->CustomerProfile = sprintf("trusted_peer=%s", $this->db->Record['gateway']);
} else {
$this->CustomerProfile = "default";
}
if (!$this->db->Record['profile_name1']) {
$log = sprintf(
"Error: customer %s (id=%d) has no weekday profile assigned in profiles table",
$this->CustomerProfile,
$this->db->Record['id']
);
syslog(LOG_NOTICE, $log);
return false;
}
if (!$this->db->Record['profile_name2']) {
$log = sprintf(
"Error: customer %s (id=%d) has no weekend profile assigned in profiles table",
$this->CustomerProfile,
$this->db->Record['id']
);
syslog(LOG_NOTICE, $log);
return false;
}
if (!$this->db->Record['timezone']) {
$log = sprintf(
"Error: missing timezone for customer %s",
$this->CustomerProfile
);
syslog(LOG_NOTICE, $log);
return false;
}
$this->billingTimezone = $this->db->Record['timezone'];
$this->allProfiles = array(
"profile_workday" => $this->db->Record['profile_name1'],
"profile_weekend" => $this->db->Record['profile_name2'],
"profile_workday_alt" => $this->db->Record['profile_name1_alt'],
"profile_weekend_alt" => $this->db->Record['profile_name2_alt'],
"timezone" => $this->db->Record['timezone']
);
if ($this->db->Record['increment']) {
$this->increment = $this->db->Record['increment'];
}
if ($this->db->Record['min_duration']) {
$this->min_duration = $this->db->Record['min_duration'];
}
return true;
} else {
$log = sprintf(
"Error: no customer found in billing_customers table for billing party=%s, domain=%s, gateway=%s",
$this->BillingPartyId,
$this->domain,
$this->gateway
);
syslog(LOG_NOTICE, $log);
return false;
}
}
private function lookupRateAudio($dayofyear, $dayofweek, $hourofday, $durationRatedAlready)
{
/*
// Required information from CDR structure
$this->BillingPartyId # calling subscriber
$this->domain # multiple callers may belong to same domain
$this->gateway # multiple callers may belong to the same gateway
$this->cNumber # E164 destination prefixed with 00 (e.g. 0041 CH)
$this->DestinationId # longest matched DestinationId
$this->region # region the destination belongs to
// pertinent to the curent rating SPAN (a span = same profile like evening hours)
$hourofday # which hour of teh day started for peak/ofpeak rates
$dayofweek # which day of the week for matching profiles
$dayofyear # which day of the year for matching holidays
$durationRatedAlready= the full duration for which a profile is defined (e.g. 0800-1800)
// the call is called recursively until the $durationRatedAlready = $CDR->duration
// when a call spans multiple profiles. If we span multiple profiles we must call
// the function again to lookup the corect rates
Rating logic
------------
1. using the profile_name found, lookup the rate_name based
on $hourofday in billing_profiles
- the day may be split in maximum 4 periods
- each day starts with hour 0 and ends with hour 24
- rate_name1 defines the first interval after hour 0
- rate_name2 defines the first interval after rate_name1
- rate_name3 defines the first interval after rate_name2
- rate_name4 defines the first interval after rate_name3
When the hour matches an interval use the rate_nameX found
to lookup the rate in billing_rates
- if no record is found use the rate called 'default'
2. lookup in billing_rates the record having same name found above
and billing_rates.destination = $this->DestinationId
- return an array with all the values to
$this->calculateAudio() function that called us
*/
// get work-day or weekend profile
if ($this->RatingTables->holidays[$dayofyear]) {
$this->profileName = $this->allProfiles['profile_weekend'];
$this->profileNameAlt = $this->allProfiles['profile_weekend_alt'];
$this->PeriodOfProfile = "weekend";
} else {
if ($dayofweek >=1 && $dayofweek <=5) {
$this->profileName = $this->allProfiles['profile_workday'];
$this->profileNameAlt = $this->allProfiles['profile_workday_alt'];
$this->PeriodOfProfile = "weekday";
} else {
$this->profileName = $this->allProfiles['profile_weekend'];
$this->profileNameAlt = $this->allProfiles['profile_weekend_alt'];
$this->PeriodOfProfile = "weekend";
}
}
// get rate for the time of the day
$timestampNextProfile = $this->timestampBilling + $durationRatedAlready;
$profileValues = $this->RatingTables->profiles[$this->profileName];
if (is_array($profileValues)) {
$this->profileNameLog = $this->profileName;
if ($hourofday < $profileValues['hour1']) {
$this->rateName = $profileValues['rate_name1'];
$this->timeInterval = "0-".$profileValues['hour1'];
$foundProfile = $profileValues['hour1'];
$this->nextProfile = $profileValues['hour1'];
} elseif ($hourofday < $profileValues['hour2']) {
$this->rateName = $profileValues['rate_name2'];
$this->timeInterval = $profileValues['hour1']."-".$profileValues['hour2'];
$foundProfile = $profileValues['hour2'];
$this->nextProfile = $profileValues['hour2'];
} elseif ($hourofday < $profileValues['hour3']) {
$this->rateName = $profileValues['rate_name3'];
$this->timeInterval = $profileValues['hour2']."-".$profileValues['hour3'];
$foundProfile = $profileValues['hour3'];
$this->nextProfile = $profileValues['hour3'];
} elseif ($hourofday < $profileValues['hour4']) {
$this->rateName = $profileValues['rate_name4'];
$this->timeInterval = $profileValues['hour3']."-".$profileValues['hour4'];
$foundProfile = $profileValues['hour4'];
$this->nextProfile = 0;
}
if ($this->rateName) {
$found_history=false;
//get historical rating if exists
if (is_array($this->RatingTables->ratesHistory[$this->rateName][$this->DestinationId][$this->application])) {
$h=0;
foreach (($this->RatingTables->ratesHistory[$this->rateName][$this->DestinationId][$this->application]) as $_idx) {
$h++;
if ($_idx['startDate'] <= $this->timestamp) {
if ($_idx['endDate'] > $this->timestamp) {
// found historical rate
$found_history=true;
$this->rateValues=$_idx;
break;
} else {
$_log = sprintf("Interval missmatch %s < %s", $_idx['endDate'], $this->timestamp);
continue;
}
} else {
$_log = sprintf("Interval missmatch %s > %s", $_idx['startDate'], $this->timestamp);
continue;
}
}
}
if (!$found_history) {
if ($this->region) {
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->region);
if (!$this->rateValues) {
// try the destination as last resort
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->DestinationId);
}
} else {
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->DestinationId);
}
}
}
}
$profileValuesAlt = $this->RatingTables->profiles[$this->profileNameAlt];
if (!$this->rateValues && is_array($profileValuesAlt)) {
$this->profileNameLog = $this->profileNameAlt;
if ($hourofday < $profileValuesAlt['hour1']) {
$this->rateName = $profileValuesAlt['rate_name1'];
$this->timeInterval = "0-".$profileValuesAlt['hour1'];
$foundProfile = $profileValuesAlt['hour1'];
$this->nextProfile = $profileValuesAlt['hour1'];
} elseif ($hourofday < $profileValuesAlt['hour2']) {
$this->rateName = $profileValuesAlt['rate_name2'];
$this->timeInterval = $profileValuesAlt['hour1']."-".$profileValuesAlt['hour2'];
$foundProfile = $profileValuesAlt['hour2'];
$this->nextProfile = $profileValuesAlt['hour2'];
} elseif ($hourofday < $profileValuesAlt['hour3']) {
$this->rateName = $profileValuesAlt['rate_name3'];
$this->timeInterval = $profileValuesAlt['hour2']."-".$profileValuesAlt['hour3'];
$foundProfile = $profileValuesAlt['hour3'];
$this->nextProfile = $profileValuesAlt['hour3'];
} elseif ($hourofday < $profileValuesAlt['hour4']) {
$this->rateName = $profileValuesAlt['rate_name4'];
$this->timeInterval = $profileValuesAlt['hour3']."-".$profileValuesAlt['hour4'];
$foundProfile = $profileValuesAlt['hour4'];
$this->nextProfile = 0;
}
if ($this->rateName) {
$found_history=false;
//get historical rating if exists
if (is_array($this->RatingTables->ratesHistory[$this->rateName][$this->DestinationId][$this->application])) {
$h=0;
foreach (($this->RatingTables->ratesHistory[$this->rateName][$this->DestinationId][$this->application]) as $_idx) {
$h++;
if ($_idx['startDate'] <= $this->timestamp) {
if ($_idx['endDate'] > $this->timestamp) {
// found historical rate
$found_history=true;
$this->rateValues=$_idx;
break;
} else {
$_log = sprintf("Interval missmatch %s < %s", $_idx['endDate'], $this->timestamp);
continue;
}
} else {
$_log = sprintf("Interval missmatch %s > %s", $_idx['startDate'], $this->timestamp);
continue;
}
}
}
if (!$found_history) {
if ($this->region) {
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->region);
// try destination as last resort
if (!$this->rateValues) {
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->DestinationId);
}
} else {
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->DestinationId);
}
}
}
}
if (!$this->rateValues) {
$this->rateNotFound=true;
$log = sprintf(
"Error: Cannot find rates for callid=%s, billing party=%s, customer %s, gateway=%s, destination=%s, profile=%s, app=%s",
$this->callId,
$this->BillingPartyId,
$this->CustomerProfile,
$this->gateway,
$this->DestinationId,
$this->profileName,
$this->application
);
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->nextProfile == "24") $this->nextProfile = 0;
$DST = Date("I", $timestampNextProfile);
if (!$this->nextProfile) {
// check it we change daylight saving time tomorrow
// yes this cann happen and we must apply a different rate
$timestampNextProfile =$timestampNextProfile+24*3600;
$DSTNext = Date("I", $timestampNextProfile);
if ($DST != $DSTNext) {
if ($DSTNext==0) {
$timestampNextProfile = $timestampNextProfile+3600;
} elseif ($DSTNext==1) {
$timestampNextProfile = $timestampNextProfile-3600;
}
}
}
// see if we have minimum duration or increment
if ($this->rateValues['increment']) {
// increase the billed duration to the next increment
$this->duration = $this->rateValues['increment'] * ceil($this->duration / $this->rateValues['increment']);
}
$durationToRate=$this->duration-$durationRatedAlready;
$month = Date("m", $timestampNextProfile);
$day = Date("d", $timestampNextProfile);
$year = Date("Y", $timestampNextProfile);
$nextProfileTimestamp = mktime($this->nextProfile, 0, 0, $month, $day, $year);
$npdt=Date("Y-m-d H:i", $nextProfileTimestamp);
$timeTillNextProfile = $nextProfileTimestamp - $this->timestampBilling;
if ($durationToRate > $timeTillNextProfile) {
$diff = $durationToRate - $timeTillNextProfile;
$this->durationRated = $timeTillNextProfile;
} else {
$this->durationRated = $durationToRate;
}
$rate = array(
"customer" => $this->CustomerProfile,
"application" => $this->application,
"profile" => $this->profileNameLog,
"day" => $this->PeriodOfProfile,
"destinationId" => $this->DestinationId,
"duration" => $this->durationRated,
"rate" => $this->rateName,
"values" => $this->rateValues,
"interval" => $this->timeInterval,
"nextHourOfDay" => $this->nextProfile
);
return $rate;
}
private function lookupRateMessage($dayofyear, $dayofweek, $hourofday)
{
/*
// Required information from CDR structure
$this->BillingPartyId # calling subscriber
$this->domain # multiple callers may belong to same domain
$this->gateway # multiple callers may belong to the same gateway
$this->cNumber # E164 destination prefixed with 00 (e.g. 0041 CH)
$this->DestinationId # longest matched DestinationId
$this->region # region the destination belongs to
// pertinent to the curent rating SPAN (a span = same profile like evening hours)
$hourofday # which hour of teh day started for peak/ofpeak rates
$dayofweek # which day of the week for matching profiles
$dayofyear # which day of the year for matching holidays
$durationRatedAlready= the full duration for which a profile is defined (e.g. 0800-1800)
// the call is called recursively until the $durationRatedAlready = $CDR->duration
// when a call spans multiple profiles. If we span multiple profiles we must call
// the function again to lookup the corect rates
Rating logic
------------
1. using the profile_name found, lookup the rate_name based
on $hourofday in billing_profiles
- the day may be split in maximum 4 periods
- each day starts with hour 0 and ends with hour 24
- rate_name1 defines the first interval after hour 0
- rate_name2 defines the first interval after rate_name1
- rate_name3 defines the first interval after rate_name2
- rate_name4 defines the first interval after rate_name3
When the hour matches an interval use the rate_nameX found
to lookup the rate in billing_rates
- if no record is found use the rate called 'default'
2. lookup in billing_rates the record having same name found above
and billing_rates.destination = $this->DestinationId
- return an array with all the values to
$this->calculateAudio() function that called us
*/
// get work-day or weekend profile
if ($this->RatingTables->holidays[$dayofyear]) {
$this->profileName = $this->allProfiles['profile_weekend'];
$this->profileNameAlt = $this->allProfiles['profile_weekend_alt'];
$this->PeriodOfProfile = "weekend";
} else {
if ($dayofweek >=1 && $dayofweek <=5) {
$this->profileName = $this->allProfiles['profile_workday'];
$this->profileNameAlt = $this->allProfiles['profile_workday_alt'];
$this->PeriodOfProfile = "weekday";
} else {
$this->profileName = $this->allProfiles['profile_weekend'];
$this->profileNameAlt = $this->allProfiles['profile_weekend_alt'];
$this->PeriodOfProfile = "weekend";
}
}
// get rate for the time of the day
$timestampNextProfile = $this->timestampBilling + $durationRatedAlready;
$profileValues = $this->RatingTables->profiles[$this->profileName];
if (is_array($profileValues)) {
$this->profileNameLog = $this->profileName;
if ($hourofday < $profileValues['hour1']) {
$this->rateName = $profileValues['rate_name1'];
$this->timeInterval = "0-".$profileValues['hour1'];
$foundProfile = $profileValues['hour1'];
} elseif ($hourofday < $profileValues['hour2']) {
$this->rateName = $profileValues['rate_name2'];
$this->timeInterval = $profileValues['hour1']."-".$profileValues['hour2'];
$foundProfile = $profileValues['hour2'];
} elseif ($hourofday < $profileValues['hour3']) {
$this->rateName = $profileValues['rate_name3'];
$this->timeInterval = $profileValues['hour2']."-".$profileValues['hour3'];
$foundProfile = $profileValues['hour3'];
} elseif ($hourofday < $profileValues['hour4']) {
$this->rateName = $profileValues['rate_name4'];
$this->timeInterval = $profileValues['hour3']."-".$profileValues['hour4'];
$foundProfile = $profileValues['hour4'];
}
if ($this->rateName) {
if ($this->region) {
$this->rateValues=$this->lookupRateValuesMessage($this->rateName, $this->region);
if (!$this->rateValues) {
// try the destination as last resort
$this->rateValues=$this->lookupRateValuesMessage($this->rateName, $this->DestinationId);
}
} else {
$this->rateValues=$this->lookupRateValuesMessage($this->rateName, $this->DestinationId);
}
}
}
$profileValuesAlt = $this->RatingTables->profiles[$this->profileNameAlt];
if (!$this->rateValues && is_array($profileValuesAlt)) {
$this->profileNameLog = $this->profileNameAlt;
if ($hourofday < $profileValuesAlt['hour1']) {
$this->rateName = $profileValuesAlt['rate_name1'];
$this->timeInterval = "0-".$profileValuesAlt['hour1'];
$foundProfile = $profileValuesAlt['hour1'];
} elseif ($hourofday < $profileValuesAlt['hour2']) {
$this->rateName = $profileValuesAlt['rate_name2'];
$this->timeInterval = $profileValuesAlt['hour1']."-".$profileValuesAlt['hour2'];
$foundProfile = $profileValuesAlt['hour2'];
} elseif ($hourofday < $profileValuesAlt['hour3']) {
$this->rateName = $profileValuesAlt['rate_name3'];
$this->timeInterval = $profileValuesAlt['hour2']."-".$profileValuesAlt['hour3'];
$foundProfile = $profileValuesAlt['hour3'];
} elseif ($hourofday < $profileValuesAlt['hour4']) {
$this->rateName = $profileValuesAlt['rate_name4'];
$this->timeInterval = $profileValuesAlt['hour3']."-".$profileValuesAlt['hour4'];
$foundProfile = $profileValuesAlt['hour4'];
}
if ($this->rateName) {
if ($this->region) {
$this->rateValues = $this->lookupRateValuesMessage($this->rateName, $this->region);
// try destination as last resort
if (!$this->rateValues) {
$this->rateValues = $this->lookupRateValuesMessage($this->rateName, $this->DestinationId);
}
} else {
$this->rateValues = $this->lookupRateValuesMessage($this->rateName, $this->DestinationId);
}
}
}
if (!$this->rateValues) {
$this->rateNotFound=true;
$log=sprintf(
"Error: Cannot find rates for callid=%s, billing party=%s, customer %s, gateway=%s, destination=%s, profile=%s, app=sms",
$this->callId,
$this->BillingPartyId,
$this->CustomerProfile,
$this->gateway,
$this->DestinationId,
$this->profileName
);
syslog(LOG_NOTICE, $log);
return false;
}
$rate = array(
"customer" => $this->CustomerProfile,
"application" => $this->application,
"profile" => $this->profileNameLog,
"day" => $this->PeriodOfProfile,
"destinationId" => $this->DestinationId,
"rate" => $this->rateName,
"values" => $this->rateValues,
);
return $rate;
}
public function MaxSessionTime($dictionary)
{
// Used for prepaid application to return maximum session time based on a prepaid balance
$this->rateValuesCache = array();
$this->MaxSessionTimeSpans = 0;
$durationRate = 0;
/////////////////////////////////////////////////////
// required fields passed from the CDR structure
//
$this->timestamp = time();
$this->callId = $dictionary['callId'];
$this->DestinationId = $dictionary['DestinationId'];
$this->BillingPartyId = $dictionary['BillingPartyId'];
$this->domain = $dictionary['domain'];
$this->duration = $dictionary['duration'];
$this->aNumber = $dictionary['aNumber'];
$this->cNumber = $dictionary['cNumber'];
$this->gateway = $dictionary['gateway'];
$this->RatingTables = $dictionary['RatingTables'];
$this->application = $dictionary['application'];
$this->ResellerId = $dictionary['ResellerId'];
$Balance = $dictionary['Balance'];
if (!$this->application) $this->application='audio';
if (!$this->DestinationId) {
$log = sprintf("Error: no DestinationId supplied in MaxSessionTime()");
syslog(LOG_NOTICE, $log);
return false;
}
if (!$this->lookupDestinationDetails()) {
return false;
}
if (!$this->lookupProfiles()) {
return false;
}
$this->startTimeBilling = getLocalTime($this->billingTimezone, $this->timestamp);
list($dateText,$timeText) = explode(" ", trim($this->startTimeBilling));
$Bdate = explode("-", $dateText);
$Btime = explode(":", $timeText);
$this->timestampBilling = mktime($Btime[0], $Btime[1], $Btime[2], $Bdate[1], $Bdate[2], $Bdate[0]);
$this->startTimeBilling = Date("Y-m-d H:i:s", $this->timestampBilling);
$i=0;
$durationRatedTotal=0;
while ($Balance > 0) {
$span++;
$this->MaxSessionTimeSpans++;
if ($i == "0") {
$dayofweek = date("w", $this->timestampBilling);
$hourofday = date("G", $this->timestampBilling);
$dayofyear = date("Y-m-d", $this->timestampBilling);
} else {
$dayofweek = date("w", $this->timestampBilling+$durationRatedTotal);
$hourofday = $foundRate['nextHourOfDay'];
$dayofyear = date("Y-m-d", $this->timestampBilling+$durationRatedTotal);
}
$foundRate = $this->lookupRateAudio($dayofyear, $dayofweek, $hourofday, $durationRatedTotal);
if ($this->rateNotFound) {
// break here to avoid loops
break;
}
$thisRate=$foundRate;
if ($j > 0) {
$payConnect=0;
$durationForRating = $thisRate['duration'];
} else {
$payConnect=1;
if ($this->min_duration && $this->duration < $this->min_duration) {
$durationForRating=$this->min_duration;
} else {
$durationForRating=$thisRate['duration'];
}
}
$j++;
$connectCost = $thisRate['values']['connectCost'];
$durationRate = $thisRate['values']['durationRate'];
if ($span=="1" && !$dictionary['skipConnectCost']) {
$this->connectCost=number_format($connectCost/$this->priceDenominator, $this->priceDecimalDigits);
$connectCostSpan=$connectCost;
$setupBalanceRequired=$connectCost/$this->priceDenominator;
if ($connectCost && $Balance <= $setupBalanceRequired) {
syslog(LOG_NOTICE, "Balance too small: $Balance <= $setupBalanceRequired");
return false;
}
$Balance = $Balance-$setupBalanceRequired;
} else {
$connectCostSpan=0;
$setupBalanceRequired=0;
}
$connectCostPrint = number_format($connectCostSpan/$this->priceDenominator, $this->priceDecimalDigits);
$durationRatePrint = number_format($durationRate/$this->priceDenominator, $this->priceDecimalDigits);
$spanPrice = $this->price+$setupBalanceRequired*$payConnect+
$durationRate*$durationForRating/$this->durationPeriodRated/$this->priceDenominator;
if ($Balance > $spanPrice) {
$Balance = $Balance-$spanPrice;
$durationRatedTotal = $durationRatedTotal+ $foundRate['duration'];
} else {
$durationAllowedinThisSpan = $Balance /
$durationRate * $this->durationPeriodRated * $this->priceDenominator;
$rateOfThisSpan=$durationRate/$this->priceDenominator;
$durationRatedTotal=$durationRatedTotal + $durationAllowedinThisSpan;
$Balance=$Balance-$spanPrice;
return $durationRatedTotal;
}
if ($durationRatedTotal >= $this->duration) {
return sprintf("%f", $durationRatedTotal);
}
$i++;
if ($i>10) {
return sprintf("%f", $durationRatedTotal);
break;
}
}
return false;
}
private function lookupRateValuesAudio($rateName, $DestinationId)
{
if (is_array($this->rateValuesCache[$rateName][$DestinationId][$this->application])) {
return $this->rateValuesCache[$rateName][$DestinationId][$this->application];
}
if ($this->settings['split_rating_table']) {
if ($rateName) {
$table="billing_rates_".$rateName;
} else {
$table="billing_rates_default";
}
$query = sprintf(
"select * from %s where destination = '%s' and application = '%s'",
addslashes($table),
addslashes($DestinationId),
addslashes($this->application)
);
} else {
$table = "billing_rates";
$query = sprintf(
"select * from %s where name = '%s' and destination = '%s' and application = '%s'",
addslashes($table),
addslashes($rateName),
addslashes($DestinationId),
addslashes($this->application)
);
}
// mysql backend
if (!$this->db->query($query)) {
if ($this->db->Errno != 1146) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
// try the main table
$query = sprintf(
"select * from billing_rates where name = '%s' and destination = '%s' and application = '%s'",
addslashes($rateName),
addslashes($DestinationId),
addslashes($this->application)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
}
if ($this->db->num_rows()) {
$this->db->next_record();
$values = array(
"connectCost" => $this->db->Record['connectCost'],
"durationRate" => $this->db->Record['durationRate'],
"connectCostIn" => $this->db->Record['connectCostIn'],
"durationRateIn" => $this->db->Record['durationRateIn']
);
// cache values
$this->rateValuesCache[$rateName][$DestinationId][$this->application] = $values;
return $values;
} else {
return false;
}
}
private function lookupRateValuesMessage($rateName, $DestinationId)
{
if (is_array($this->rateValuesCache[$rateName][$DestinationId]['sms'])) {
return $this->rateValuesCache[$rateName][$DestinationId]['sms'];
}
if ($this->settings['split_rating_table']) {
if ($rateName) {
$table = "billing_rates_".$rateName;
} else {
$table = "billing_rates_default";
}
$query = sprintf(
"select * from %s where (destination = '%s' or destination = '') and application = 'sms' order by destination desc limit 1",
addslashes($table),
addslashes($DestinationId)
);
} else {
$table = "billing_rates";
$query = sprintf(
"select * from %s where name = '%s' and (destination = '%s' or destination = '') and application = 'sms' order by destination desc limit 1",
addslashes($table),
addslashes($rateName),
addslashes($DestinationId)
);
}
// mysql backend
if (!$this->db->query($query)) {
if ($this->db->Errno != 1146) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
// try the main table
// lookup rate from MySQL
$query = sprintf(
"select * from billing_rates where name = '%s' and (destination = '%s' or destination = '') and application = 'sms' order by destination desc limit 1",
addslashes($rateName),
addslashes($DestinationId)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
}
if ($this->db->num_rows()) {
$this->db->next_record();
$values = array(
"connectCost" => $this->db->Record['connectCost']
);
// cache values
$this->rateValuesCache[$rateName][$DestinationId]['sms']=$values;
return $values;
} else {
return false;
}
}
}
class RatingTables
{
var $database_backend = 'mysql';
var $csv_export=array(
"destinations" => "destinations.csv",
"billing_customers" => "customers.csv",
"billing_profiles" => "profiles.csv",
"billing_rates" => "rates.csv",
"billing_rates_history" => "ratesHistory.csv",
"billing_discounts" => "discounts.csv",
"billing_enum_tlds" => "enumtld.csv",
"prepaid" => "prepaid.csv",
"quota_usage" => "quotausage.csv"
);
var $csv_import = array(
"destinations" => "destinations.csv",
"billing_customers" => "customers.csv",
"billing_profiles" => "profiles.csv",
"billing_rates" => "rates.csv",
"billing_rates_history" => "ratesHistory.csv",
"billing_discounts" => "discounts.csv"
);
var $previously_imported_files = 0;
var $maxrowsperpage = 15;
var $insertDomainOption = array();
var $delimiter = ",";
var $filesToImport = array();
var $importFilesPatterns = array(
'ratesHistory',
'rates',
'profiles',
'destinations',
'discounts',
'customers'
);
var $mustReload = false;
var $web_elements = array(
'table',
'export',
'web_task',
'subweb_task',
'confirmDelete',
'confirmCopy',
'next',
'id',
'search_text',
'ReloadRatingTables',
'account',
'balance',
'fromRate',
'toRate',
'sessionId'
);
var $requireReload = array('destinations');
var $whereResellerFilter = " (1=1) ";
var $cvs_import_dir = "/var/spool/cdrtool";
var $tables = array(
"destinations" => array(
"name" => "Destinations",
"skip_math" => true,
"keys" => array(
"id"
),
"exceptions" => array(),
"order" => "dest_id ASC",
"domainFilterColumn" => "domain",
"fields" => array(
"gateway" => array(
"size" => 15,
"checkType" => 'ip',
"name" => "Trusted peer"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"domain" => array(
"size" => 15,
"name" => "Domain",
"checkType" => 'domain',
"class" => "span2"
),
"subscriber" => array(
"size" => 15,
"checkType" => 'sip_account',
"name" => "Subscriber",
"class" => "span2"
),
"dest_id" => array(
"size" => 12,
"name" => "Destination",
),
"region" => array(
"size" => 10,
"name" => "Region"
),
"dest_name" => array(
"size" => 20,
"name" => "Description",
"class" => "span2"
),
"increment" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "Incr"
),
"min_duration" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "Min Dur"
),
"max_duration" => array(
"size" => 5,
"checkType" => 'numeric',
"name" => "Max Dur"
),
"max_price" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Max Price"
)
)
),
"billing_customers" => array(
"name" => "Customers",
"skip_math" => true,
"keys" => array("id"),
"domainFilterColumn" => "domain",
"fields" => array(
"gateway" => array(
"size" => 15,
"checkType" => 'ip',
"name" => "Trusted Peer"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"domain" => array(
"size" => 15,
"checkType" => 'domain',
"name" => "Domain",
"class" => "span2"
),
"subscriber" => array(
"size" => 25,
"checkType" => 'sip_account',
"name" => "Subscriber",
"class" => "span2"
),
"profile_name1" => array(
"size" => 10,
"name" => "Profile WD"
),
"profile_name1_alt" => array(
"size" => 8,
"name" => "Fallback"
),
"profile_name2" => array(
"size" => 10,
"name" => "Profile WE"
),
"profile_name2_alt" => array(
"size" => 8,
"name" => "Fallback"
),
"timezone" => array(
"size" => 16,
"name" => "Timezone",
"class" => "span2"
),
"increment" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "Incr"
),
"min_duration" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "Min Dur"
)
)
),
"billing_discounts" => array(
"name" => "Discounts",
"keys" => array("id"),
"domainFilterColumn" => "domain",
"fields" => array(
"gateway" => array(
"size" => 15,
"checkType" => 'ip',
"name" => "Trusted Peer"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"domain" => array(
"size" => 15,
"checkType" => 'domain',
"name" => "Domain",
"class" => "span2"
),
"subscriber" => array(
"size" => 25,
"checkType" => 'sip_account',
"name" => "Subscriber",
"class" => "span2"
),
"application" => array(
"size" => 6,
"name" => "App"
),
"destination" => array(
"size" => 10,
"name" => "Destination"
),
"region" => array(
"size" => 8,
"name" => "Region"
),
"connect" => array(
"size" => 5,
"name" => "Connect"
),
"duration" => array(
"size" => 5,
"name" => "Duration"
)
)
),
"billing_profiles" => array(
"name" => "Profiles",
"skip_math" => true,
"keys" => array("id"),
"exceptions" => array(),
"size" => 6,
"fields" => array(
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"name" => array(
"size" => 12,
"name" => "Profile",
"class" => "span2"
),
"rate_name1" => array(
"size" => 12,
"name" => "Rate 1"
),
"hour1" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "00-H1"
),
"rate_name2" => array(
"size" => 12,
"name" => "Rate 2"
),
"hour2" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "H1-H2"
),
"rate_name3" => array(
"size" => 12,
"name" => "Rate 3"
),
"hour3" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "H2-H3"
),
"rate_name4" => array(
"size" => 12,
"name" => "Rate 4"
),
"hour4" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "H3-24"
),
)
),
"billing_rates" => array(
"name" => "Rates",
"keys" => array("id"),
"size" => 10,
"exceptions" => array('maxPrice'),
"order" => "durationRate desc",
"fields" => array(
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"name" => array(
"size" => 12,
"name" => "Rate",
"class" => "span2"
),
"destination" => array(
"size" => 12,
"name" => "Destination"
),
"application" => array(
"size" => 6,
"name" => "App"
),
"connectCost" => array(
"size" => 8,
"checkType" => 'numeric',
"name"=>"Connect"
),
"durationRate" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Duration"
),
"connectCostIn" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Conn In"
),
"durationRateIn" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Duration In"
)
)
),
"billing_rates_history" => array(
"name" => "Rates history",
"keys" => array("id"),
"size" => 10,
"order" => "destination ASC, name ASC",
"fields" => array(
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"name" => array(
"size" => 10,
"name" => "Rate",
"class" => "span2"
),
"destination" => array(
"size" => 12,
"name" => "Destination"
),
"application" => array(
"size" => 6,
"name" => "App"
),
"connectCost" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Conn"
),
"durationRate" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Price"
),
"connectCostIn" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Conn In"
),
"durationRateIn" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Price In"
),
"startDate" => array(
"size" => 11,
"name" => "Start Date",
"class" => "span2"
),
"endDate" => array(
"size" => 11,
"name" => "End Date",
"class" => "span2"
)
)
),
"billing_enum_tlds" => array(
"name" => "ENUM discounts",
"skip_math" => true,
"keys" => array("id"),
"exceptions" => array(),
"size" => 6,
"fields" => array(
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"enum_tld" => array(
"size" => 35,
"mustExist" => true,
"checkType" => 'domain',
"name" => "ENUM TLD",
"class" => "span2"
),
"e164_regexp" => array(
"size" => 35,
"mustExist" => true,
"name" => "E164 Regexp",
"class" => "span2"
),
"discount" => array(
"size" => 10,
"mustExist" => true,
"checkType" => 'numeric',
"name" => "Discount"
)
)
),
"prepaid" => array(
"name" => "Prepaid accounts",
"keys" => array("id"),
"size" => 15,
"exceptions" => array('change_date','active_sessions','domain'),
"order" => "change_date DESC",
"fields" => array(
"account" => array(
"size" => 35,
"name" => "Subscriber",
"checkType" => 'sip_account',
"mustExist" => true,
"class" => "span2"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"balance" => array(
"size" => 10,
"name" => "Balance"
),
"change_date" => array(
"size" => 19,
"name" => "Last Change",
"readonly" => 1
),
"session_counter" => array(
"size" => 3,
"name" => "Active Sessions",
"readonly" => 1
),
"max_sessions" => array(
"size" => 3,
"name" => "Max Sessions"
)
)
),
"prepaid_cards" => array(
"name" => "Prepaid cards",
"keys" => array("id"),
"size" => 15,
"exceptions" => array('service'),
"fields" => array(
"batch" => array(
"size" => 40,
"name" => "Batch name",
"readonly" => 1,
"class" => "span3"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"date_batch" => array(
"size" => 11,
"name" => "Batch Date",
"class" => "span2"
),
"number" => array(
"size" => 20,
"checkType" => 'numeric',
"mustExist" => true,
"name" => "Card Number",
"class" => "span2"
),
"id" => array(
"size" => 20,
"checkType" => 'numeric',
"mustExist" => true,
"name" => "Card Id",
),
"value" => array(
"size" => 8,
"checkType" => 'numeric',
"mustExist" => true,
"name" => "Card Value"
),
"blocked" => array(
"size" => 1,
"name" => "Lock"
),
"date_active" => array(
"size" => 18,
"name" => "Activation Date",
"class" => "span2"
)
)
),
"prepaid_history" => array(
"name" => "Prepaid history",
"order" => "id DESC",
"skip_math" => true,
"keys" => array("id"),
"size" => 15,
"exceptions" => array('session','destination'),
"fields" => array(
"username" => array(
"size" => 15,
"readonly" => 1,
"class" => "span2"
),
"domain" => array(
"size" => 15,
"readonly" => 1,
"class" => "span2"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller",
"readonly" => 1
),
"action" => array(
"size" => 15,
"readonly" => 1,
"class" => "span2"
),
"duration" => array(
"size" => 5
),
"destination" => array(
"size" => 15
),
"session" => array(
"size" => 30,
"readonly" => 1
),
"description" => array(
"size" => 30,
"class" => "span3"
),
"value" => array(
"size" => 10
),
"balance" => array(
"size" => 10
),
"date" => array(
"size" => 18,
"class" => "span2"
)
)
),
"quota_usage" => array(
"name" => "Quota usage",
"keys" => array("id"),
"size" => 15,
"readonly" => 1,
"exceptions" => array(
"change_date",
"traffic",
"duration",
"calls"
),
"domainFilterColumn" => "domain",
"fields" => array(
"datasource" => array(
"size" => 15,
"readonly" => 1
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller",
"readonly" => true
),
"account" => array(
"size" => 30,
"readonly" => 1,
"name" => "Subscriber",
"class" => "span2"
),
"domain" => array(
"size" => 15,
"readonly" => 1,
"class" => "span2"
),
"blocked" => array(
"size" => 2,
"readonly" => 1
),
"notified" => array(
"size" => 20,
"readonly" => 1
),
"quota" => array(
"size" => 5,
"readonly" => 1
),
"cost" => array(
"size" => 10,
"readonly" => 1,
"name" => "This Month"
),
"cost_today" => array(
"size" => 10,
"readonly" => 1,
"name" => "Today"
),
"duration" => array(
"size" => 10,
"readonly" => 1
),
"calls" => array(
"size" => 10,
"readonly" => 1
),
"traffic" => array(
"size" => 20,
"readonly" => 1
)
)
)
);
- public function RatingTables($readonly = false)
+ public function __construct($readonly = false)
{
global $CDRTool;
global $RatingEngine;
$this->settings = $RatingEngine;
$this->CDRTool = $CDRTool;
$this->table = $_REQUEST['table'];
if (!$this->table || !in_array($this->table, array_keys($this->tables))) {
$this->table="destinations";
}
$this->readonly=$readonly;
if ($this->settings['csv_delimiter']) {
$this->delimiter=$this->settings['csv_delimiter'];
}
if (!strlen($this->CDRTool['filter']['customer'])) {
$this->whereResellerFilter = sprintf("reseller_id = %d", '99999999');
} else {
if ($this->CDRTool['filter']['customer'] && $this->tables[$this->table]['fields']['reseller_id']) {
$this->whereResellerFilter = sprintf("reseller_id = %d", addslashes($this->CDRTool['filter']['customer']));
$this->tables[$this->table]['fields']['reseller_id']['readonly']=true;
}
}
if ($this->settings['split_rating_table']) {
$this->tables['billing_rates']['fields']['name']['readonly'] = 1;
}
if (strlen($this->settings['socketIP'])) {
if ($this->settings['socketIP'] == '0.0.0.0' || $this->settings['socketIP'] == '0') {
$this->settings['socketIPforClients'] = '127.0.0.1';
} else {
$this->settings['socketIPforClients'] = $this->settings['socketIP'];
}
}
if ($this->settings['database_backend']) {
$this->database_backend = $this->settings['database_backend'];
}
$this->db = new DB_cdrtool;
$this->db1 = new DB_cdrtool;
$this->db->Halt_On_Error="no";
$this->db1->Halt_On_Error="no";
}
public function ImportCSVFiles($dir = false)
{
$results = 0;
if (!$dir) $dir = "/var/spool/cdrtool";
$this->scanFilesForImport($dir);
if ($this->previously_imported_files) {
printf("Skipping %d previously imported files\n", $this->previously_imported_files);
}
$results=0;
foreach (array_keys($this->filesToImport) as $file) {
$importFunction = "Import".ucfirst($this->filesToImport[$file]['type']);
printf("Reading file %s\n", $this->filesToImport[$file]['path']);
$results = $this->$importFunction($this->filesToImport[$file]['path'],$this->filesToImport[$file]['reseller']);
$this->logImport(
$dir,
$this->filesToImport[$file]['path'],
$this->filesToImport[$file]['watermark'],
$results,
$this->filesToImport[$file]['reseller']
);
}
return $results;
}
private function ImportRates($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
printf("Importing rates from %s for reseller %s:\n", $file, $reseller);
while ($buffer = fgets($fp, 1024)) {
$buffer = trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$name = trim($p[2]);
$destination = trim($p[3]);
$application = trim($p[4]);
$connectCost = trim($p[5]);
$durationRate = trim($p[6]);
$connectCostIn = trim($p[7]);
$durationRateIn = trim($p[8]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (!is_numeric($destination) && !strstr($destination, '@')) {
// skip invalid destinations
$skipped++;
continue;
}
if (strlen($connectCost) && !is_numeric($connectCost)) {
$skipped++;
continue;
}
if (strlen($durationRate) && !is_numeric($durationRate)) {
$skipped++;
continue;
}
if (!$application) $application='audio';
if ($ops=="1") {
$query = sprintf(
"insert into billing_rates
(
reseller_id,
name,
destination,
application,
connectCost,
durationRate,
connectCostIn,
durationRateIn
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn)
);
// mysql backend
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
if ($this->db->affected_rows()) {
if ($this->settings['split_rating_table']) {
if ($name) {
$_table = 'billing_rates_'.$name;
} else {
$_table = 'billing_rates_default';
}
if (!$this->createRatingTable($name)) {
$query = sprintf(
"insert into %s
(
id,
reseller_id,
name,
destination,
application,
connectCost,
durationRate,
connectCostIn,
durationRateIn
) values (
LAST_INSERT_ID(),
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($_table),
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
}
}
$inserted++;
} else {
$failed++;
}
} elseif ($ops == "3") {
$query = sprintf(
"delete from billing_rates
where
reseller_id = '%s'
and name = '%s'
and destination = '%s'
and application = '%s'",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application)
);
// mysql backend
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
if ($this->settings['split_rating_table']) {
if ($name) {
$_table = 'billing_rates_'.$name;
} else {
$_table = 'billing_rates_default';
}
$query = sprintf(
"delete from %s
where reseller_id = '%s'
and name = '%s'
and destination = '%s'
and application = '%s'",
addslashes($_table),
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
}
$deleted++;
}
} elseif ($ops == "2") {
$query = sprintf(
"select * from billing_rates
where name = '%s'
and destination = '%s'
and reseller_id = '%s'
and application = '%s'
",
addslashes($name),
addslashes($destination),
addslashes($reseller_id),
addslashes($application)
);
// mysql backend
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update billing_rates set
connectCost = '%s',
durationRate = '%s',
connectCostIn = '%s',
durationRateIn = '%s'
where name = '%s'
and destination = '%s'
and reseller_id = '%s'
and application = '%s'
",
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn),
addslashes($name),
addslashes($destination),
addslashes($reseller_id),
addslashes($application)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
if ($this->settings['split_rating_table']) {
if ($name) {
$_table = 'billing_rates_'.$name;
} else {
$_table = 'billing_rates_default';
}
$query = sprintf(
"update %s set
connectCost = '%s',
durationRate = '%s',
connectCostIn = '%s',
durationRateIn = '%s'
where name = '%s'
and destination = '%s'
and reseller_id = '%s'
and application = '%s'
",
addslashes($_table),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn),
addslashes($name),
addslashes($destination),
addslashes($reseller_id),
addslashes($application)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
}
$updated++;
}
} else {
$query = sprintf(
"insert into billing_rates
(
reseller_id,
name,
destination,
application,
connectCost,
durationRate,
connectCostIn,
durationRateIn
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
if ($this->settings['split_rating_table']) {
if ($name) {
$_table = 'billing_rates_'.$name;
} else {
$_table = 'billing_rates_default';
}
if (!$this->createRatingTable($name)) {
$query = sprintf(
"insert into %s
(
id,
reseller_id,
name,
destination,
application
connectCost,
durationRate,
connectCostIn,
durationRateIn
) values (
LAST_INSERT_ID(),
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($_table),
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
}
}
$inserted++;
} else {
$failed++;
}
}
} else {
$skipped++;
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($skipped) print "Skipped $skipped records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results = $inserted+$updated+$deleted;
return $results;
}
private function ImportRatesHistory($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$this->mustReload=true;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
printf("Importing rates history from %s for reseller %s:\n", $file, $reseller);
while ($buffer = fgets($fp, 1024)) {
$buffer=trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$name = trim($p[2]);
$destination = trim($p[3]);
$application = trim($p[4]);
$connectCost = trim($p[5]);
$durationRate = trim($p[6]);
$connectCostIn = trim($p[7]);
$durationRateIn = trim($p[8]);
$startDate = trim($p[9]);
$endDate = trim($p[10]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (!is_numeric($destination) && !strstr($destination, '@')) {
// skip invalid destinations
$skipped++;
continue;
}
if (strlen($connectCost) && !is_numeric($connectCost)) {
$skipped++;
continue;
}
if (strlen($durationRate) && !is_numeric($durationRate)) {
$skipped++;
continue;
}
if (preg_match("/^\d{4}\-{\d{2}\-\d{2}$/", $startDate)) {
$skipped++;
continue;
}
if (preg_match("/^\d{4}\-{\d{2}\-\d{2}$/", $endDate)) {
$skipped++;
continue;
}
if ($ops=="1") {
$query = sprintf(
"insert into billing_rates_history
(
reseller_id,
name,
destination,
application,
connectCost,
durationRate,
connectCostIn,
durationRateIn,
startDate,
endDate
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn),
addslashes($startDate),
addslashes($endDate)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
} elseif ($ops=="3") {
$query = sprintf(
"delete from billing_rates_history
where reseller_id = '%s'
and name = '%s'
and destination = '%s'
and startDate = '%s'
and endDate = '%s'",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($startDate),
addslashes($endDate)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$deleted++;
}
} elseif ($ops=="2") {
$query = sprintf(
"select * from billing_rates_history
where name = '%s'
and destination = '%s'
and reseller_id = '%s'
and startDate = '%s'
and endDate = '%s'
",
addslashes($name),
addslashes($destination),
addslashes($reseller_id),
addslashes($startDate),
addslashes($endDate)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update billing_rates_history set
application = '%s',
connectCost = '%s',
durationRate = '%s',
connectCostIn = '%s',
connectCostIn = '%s'
where name = '%s'
and destination = '%s'
and reseller_id = '%s'
and startDate = '%s'
and endDate = '%s'
",
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn),
addslashes($name),
addslashes($destination),
addslashes($reseller_id),
addslashes($startDate),
addslashes($endDate)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$updated++;
}
} else {
$query = sprintf(
"insert into billing_rates_history
(
reseller_id,
name,
destination,
application,
connectCost,
durationRate,
connectCostIn,
durationRateIn,
startDate,
endDate
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn),
addslashes($startDate),
addslashes($endDate)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
}
} else {
$skipped++;
}
$j++;
if ($j=="10000") {
flush();
$j=0;
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($skipped) print "Skipped $skipped records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results = $inserted + $updated + $deleted;
return $results;
}
private function ImportCustomers($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$this->mustReload = true;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
printf("Importing customers from %s for reseller %s:\n", $file, $reseller);
while ($buffer = fgets($fp, 1024)) {
$buffer=trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$gateway = trim($p[2]);
$domain = trim($p[3]);
$subscriber = trim($p[4]);
$profile_name1 = trim($p[5]);
$profile_name1_alt = trim($p[6]);
$profile_name2 = trim($p[7]);
$profile_name2_alt = trim($p[8]);
$timezone = trim($p[9]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (strlen($reseller_id) && !is_integer($reseller_id)) {
$skipped++;
continue;
}
if ($ops=="1") {
$query = sprintf(
"insert into billing_customers
(
reseller_id,
gateway,
domain,
subscriber,
profile_name1,
profile_name2,
timezone,
profile_name1_alt,
profile_name2_alt
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($profile_name1),
addslashes($profile_name2),
addslashes($timezone),
addslashes($profile_name1_alt),
addslashes($profile_name2_alt)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
} elseif ($ops == "3") {
$query = sprintf(
"delete from billing_customers
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$deleted++;
}
} elseif ($ops == "2") {
$query = sprintf(
"select * from billing_customers
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update billing_customers set
profile_name1 = '%s',
profile_name2 = '%s',
profile_name1_alt = '%s',
profile_name2_alt = '%s',
timezone = '%s'
where gateway = '%s'
and domain = '%s'
and reseller_id = '%s'
and subscriber = '%s'\n",
addslashes($profile_name1),
addslashes($profile_name2),
addslashes($profile_name1_alt),
addslashes($profile_name2_alt),
addslashes($timezone),
addslashes($gateway),
addslashes($domain),
addslashes($reseller_id),
addslashes($subscriber)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
$updated++;
}
} else {
$query = sprintf(
"insert into billing_customers
(
reseller_id,
gateway,
domain,
subscriber,
profile_name1,
profile_name2,
timezone,
profile_name1_alt,
profile_name2_alt
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($profile_name1),
addslashes($profile_name2),
addslashes($timezone),
addslashes($profile_name1_alt),
addslashes($profile_name2_alt)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
$inserted++;
}
}
} else {
$skipped++;
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($skipped) print "Skipped $skipped records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results=$inserted+$updated+$deleted;
return $results;
}
private function ImportDestinations($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$this->mustReload=true;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
printf("Importing destinations from %s for reseller %s:\n", $file, $reseller);
while ($buffer = fgets($fp, 1024)) {
$buffer=trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$gateway = trim($p[2]);
$domain = trim($p[3]);
$subscriber = trim($p[4]);
$dest_id = trim($p[5]);
$region = trim($p[6]);
$dest_name = trim($p[7]);
$increment = intval($p[8]);
$min_duration = intval($p[9]);
$max_duration = intval($p[10]);
$max_price = trim($p[11]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (!is_numeric($dest_id) && !strstr($dest_id, '@')) {
// skip invalid destinations
$skipped++;
continue;
}
if ($ops=="1") {
$query = sprintf(
"insert into destinations
(
reseller_id,
gateway,
domain,
subscriber,
dest_id,
region,
dest_name,
increment,
min_duration,
max_duration,
max_price
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($dest_id),
addslashes($region),
addslashes($dest_name),
addslashes($increment),
addslashes($min_duration),
addslashes($max_duration),
addslashes($max_price)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
} elseif ($ops == "3") {
$query = sprintf(
"delete from destinations
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and dest_id = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($dest_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$deleted++;
}
} elseif ($ops == "2") {
$query = sprintf(
"select * from destinations
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and dest_id = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($dest_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update destinations set
region = '%s',
dest_name = '%s',
increment = '%s',
min_duration = '%s',
max_duration = '%s',
max_price = '%s'
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and dest_id = '%s'
",
addslashes($region),
addslashes($dest_name),
addslashes($increment),
addslashes($min_duration),
addslashes($max_duration),
addslashes($max_price),
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($dest_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
$updated++;
}
} else {
$query = sprintf(
"insert into destinations
(
reseller_id,
gateway,
domain,
subscriber,
dest_id,
region,
dest_name,
increment,
min_duration,
max_duration,
max_price
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($dest_id),
addslashes($region),
addslashes($dest_name),
addslashes($increment),
addslashes($min_duration),
addslashes($max_duration),
addslashes($max_price)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
}
} else {
$skipped++;
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($skipped) print "Skipped $skipped records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results = $inserted + $updated + $deleted;
return $results;
}
private function ImportDiscounts($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$this->mustReload=true;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
printf("Importing discounts from %s for reseller %s:\n", $file, $reseller);
while ($buffer = fgets($fp, 1024)) {
$buffer=trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$gateway = trim($p[2]);
$domain = trim($p[3]);
$subscriber = trim($p[4]);
$application = trim($p[5]);
$destination = trim($p[6]);
$region = trim($p[7]);
$connect = intval($p[8]);
$duration = intval($p[9]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (!is_numeric($destination) && !strstr($destination, '@')) {
// skip invalid destinations
$skipped++;
continue;
}
if ($ops == "1") {
$query = sprintf(
"insert into billing_discounts
(
reseller_id,
gateway,
domain,
subscriber,
application,
destination,
region,
connect,
duration
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($application),
addslashes($destination),
addslashes($region),
addslashes($connect),
addslashes($duration)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
} elseif ($ops == "3") {
$query=sprintf(
"delete from billing_discounts
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and application = '%s'
and destination = '%s'
and region = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($application),
addslashes($destination),
addslashes($region)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$deleted++;
}
} elseif ($ops == "2") {
$query = sprintf(
"select * from billing_discounts
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and application = '%s'
and destination = '%s'
and region = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($application),
addslashes($destination),
addslashes($region)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update billing_discounts set
connect = '%s',
duration = '%s',
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and application = '%s'
and destination = '%s'
and region = '%s'
",
addslashes($connect),
addslashes($duration),
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($application),
addslashes($destination),
addslashes($region)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
$updated++;
}
} else {
$query = sprintf(
"insert into billing_discounts
(
reseller_id,
gateway,
domain,
subscriber,
application,
destination,
region,
connect,
duration
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($application),
addslashes($destination),
addslashes($region),
addslashes($connect),
addslashes($duration)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
}
} else {
$skipped++;
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($skipped) print "Skipped $skipped records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results = $inserted + $updated + $deleted;
return $results;
}
private function ImportProfiles($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$this->mustReload=true;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
print "Importing Profiles:\n";
while ($buffer = fgets($fp, 1024)) {
$buffer=trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$profile = trim($p[2]);
$rate1 = trim($p[3]);
$hour1 = trim($p[4]);
$rate2 = trim($p[5]);
$hour2 = trim($p[6]);
$rate3 = trim($p[7]);
$hour3 = trim($p[8]);
$rate4 = trim($p[9]);
$hour4 = trim($p[10]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (!$hour1) $hour1=0;
if (!$hour2) $hour2=0;
if (!$hour3) $hour3=0;
if (!$hour4) $hour4=0;
if ($ops=="1") {
$query = sprintf(
"insert into billing_profiles
(
reseller_id,
name,
rate_name1,
hour1,
rate_name2,
hour2,
rate_name3,
hour3,
rate_name4,
hour4
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($profile),
addslashes($rate1),
addslashes($hour1),
addslashes($rate2),
addslashes($hour2),
addslashes($rate3),
addslashes($hour3),
addslashes($rate4),
addslashes($hour4)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
} elseif ($ops == "3") {
$query = sprintf(
"delete from billing_profiles
where name = '%s'
and reseller_id= '%s'
",
addslashes($profile),
addslashes($reseller_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$deleted++;
}
} elseif ($ops == "2") {
$query = sprintf(
"select * from billing_profiles
where name = '%s'
and reseller_id= '%s'
",
addslashes($profile),
addslashes($reseller_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update billing_profiles set
rate_name1 = '%s',
rate_name2 = '%s',
rate_name3 = '%s',
rate_name4 = '%s',
hour1 = '%s',
hour2 = '%s',
hour3 = '%s',
hour4 = '%s'
where name = '%s'
and reseller_id= '%s'
\n",
addslashes($rate1),
addslashes($rate2),
addslashes($rate3),
addslashes($rate4),
addslashes($hour1),
addslashes($hour2),
addslashes($hour3),
addslashes($hour4),
addslashes($profile),
addslashes($reseller_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
$updated++;
}
} else {
$query = sprintf(
"insert into billing_profiles
(
reseller_id,
name,
rate_name1,
hour1,
rate_name2,
hour2,
rate_name3,
hour3,
rate_name4,
hour4
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($profile),
addslashes($rate1),
addslashes($hour1),
addslashes($rate2),
addslashes($hour2),
addslashes($rate3),
addslashes($hour3),
addslashes($rate4),
addslashes($hour4)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
}
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results = $inserted + $updated + $deleted;
return $results;
}
public function LoadRatingTables()
{
$log = sprintf(
"Memory usage: %0.2fMB, memory limit: %sB",
memory_get_usage() / 1024 / 1024,
ini_get('memory_limit')
);
syslog(LOG_NOTICE, $log);
$loaded['profiles'] = $this->LoadProfilesTable();
$loaded['ratesHistory'] = $this->LoadRatesHistoryTable();
$loaded['holidays'] = $this->LoadHolidaysTable();
$loaded['enumTlds'] = $this->LoadENUMtldsTable();
foreach (array_keys($loaded) as $_load) {
syslog(LOG_NOTICE, "Loaded $loaded[$_load] $_load into memory");
}
$log = sprintf(
"Memory usage: %0.2fMB, memory limit: %sB",
memory_get_usage() / 1024 / 1024,
ini_get('memory_limit')
);
syslog(LOG_NOTICE, $log);
return $loaded;
}
private function LoadENUMtldsTable()
{
$query = "select * from billing_enum_tlds";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$i=0;
$rows=$this->db->num_rows();
while ($this->db->next_record()) {
if ($this->db->Record['enum_tld']) {
$i++;
$_app=$this->db->Record['application'];
if (!$_app) $_app='audio';
$_ENUMtlds[$this->db->Record['enum_tld']] = array(
"discount" => $this->db->Record['discount'],
"e164_regexp" => $this->db->Record['e164_regexp']
);
}
}
$this->ENUMtlds = $_ENUMtlds;
$this->ENUMtldsCount = $i;
return $i;
}
private function LoadRatesHistoryTable()
{
$query = "select *,
UNIX_TIMESTAMP(startDate) as startDateTimestamp,
UNIX_TIMESTAMP(endDate) as endDateTimestamp
from billing_rates_history
order by name ASC,destination ASC,startDate DESC";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$i=0;
$rows=$this->db->num_rows();
while ($this->db->next_record()) {
if ($this->db->Record['name'] && $this->db->Record['destination']) {
$i++;
$_app = $this->db->Record['application'];
if (!$_app) $_app='audio';
$_rates[$this->db->Record['name']][$this->db->Record['destination']][$_app][$this->db->Record['id']]=
array(
"connectCost" => $this->db->Record['connectCost'],
"durationRate" => $this->db->Record['durationRate'],
"connectCostIn" => $this->db->Record['connectCostIn'],
"durationRateIn" => $this->db->Record['durationRateIn'],
"increment" => $this->db->Record['increment'],
"min_duration" => $this->db->Record['min_duration'],
"startDate" => $this->db->Record['startDateTimestamp'],
"endDate" => $this->db->Record['endDateTimestamp']
);
}
}
$this->ratesHistory = $_rates;
$this->ratesHistoryCount = $i;
return $i;
}
private function LoadProfilesTable()
{
$query = "select * from billing_profiles order by name";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$i=0;
while ($this->db->next_record()) {
$i++;
if ($this->db->Record['name'] && $this->db->Record['hour1'] > 0) {
$_profiles[$this->db->Record['name']]=
array(
"rate_name1" => $this->db->Record['rate_name1'],
"hour1" => $this->db->Record['hour1'],
"rate_name2" => $this->db->Record['rate_name2'],
"hour2" => $this->db->Record['hour2'],
"rate_name3" => $this->db->Record['rate_name3'],
"hour3" => $this->db->Record['hour3'],
"rate_name4" => $this->db->Record['rate_name4'],
"hour4" => $this->db->Record['hour4'],
);
}
}
$this->profiles=$_profiles;
return $i;
}
private function LoadHolidaysTable()
{
$query="select * from billing_holidays order by day";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$i=0;
while ($this->db->next_record()) {
if ($this->db->Record['day']) {
$i++;
$_holidays[$this->db->Record['day']]++;
}
}
$this->holidays=$_holidays;
return $i;
}
public function checkRatingEngineConnection()
{
if ($this->settings['socketIPforClients'] && $this->settings['socketPort']
&& $fp = fsockopen($this->settings['socketIPforClients'], $this->settings['socketPort'], $errno, $errstr, 2)
) {
fclose($fp);
return true;
}
return false;
}
function showCustomers($filter)
{
return true;
foreach (array_keys($this->customers) as $key) {
if (strlen($filter)) {
if (preg_match("/$filter/", $key)) {
$customers = $customers.$key."\n";
}
} else {
$customers = $customers.$key."\n";
}
}
return $customers;
}
public function showProfiles()
{
foreach (array_keys($this->profiles) as $key) {
$profiles=$profiles.$key."\n";
}
return $profiles;
}
public function showENUMtlds()
{
foreach (array_keys($this->ENUMtlds) as $key) {
$ENUMtlds=$ENUMtlds.$key."\n";
}
return $ENUMtlds;
}
private function scanFilesForImport($dir)
{
$import_dirs[$this->cvs_import_dir] = array(
'path' => $this->cvs_import_dir,
'reseller' => 0
);
if ($handle = opendir($this->cvs_import_dir)) {
while (false !== ($filename = readdir($handle))) {
$reseller=0;
if ($filename == "." || $filename == "..") continue;
$fullPath = $this->cvs_import_dir."/".$filename;
if (is_dir($fullPath) && is_numeric($filename)) {
$reseller = $filename;
$import_dirs[$fullPath]=array(
'path' => $fullPath,
'reseller'=> $reseller
);
}
}
}
foreach (array_keys($import_dirs) as $_dir) {
if ($handle = opendir($_dir)) {
while (false !== ($filename = readdir($handle))) {
if ($filename != "." && $filename != "..") {
foreach ($this->importFilesPatterns as $_pattern) {
if (strstr($filename, $_pattern) && preg_match("/\.csv$/", $filename)) {
$fullPath = $_dir."/".$filename;
if ($content = file_get_contents($fullPath)) {
$watermark = $filename."-".md5($content);
if ($this->hasFileBeenImported($filename, $watermark)) {
$this->previously_imported_files++;
break;
}
$this->filesToImport[$filename] = array(
'name' => $filename,
'watermark' => $watermark,
'type' => $_pattern,
'path' => $fullPath,
'reseller' => $import_dirs[$_dir]['reseller']
);
}
break;
}
}
}
}
}
}
}
private function hasFileBeenImported($filename, $watermark)
{
$query = sprintf(
"select * from log where url = '%s'\n",
addslashes($watermark)
);
if ($this->db->query($query)) {
if ($this->db->num_rows()) {
$this->db->next_record();
/*
$log=sprintf ("File %s has already been imported at %s.\n",$filename,$this->db->f('date'));
syslog(LOG_NOTICE, $log);
print $log;
*/
return true;
} else {
return false;
}
} else {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
}
private function logImport($dir, $filename, $watermark, $results = 0, $reseller = 0)
{
$query = sprintf(
"insert into log (
date,
login,
ip,
url,
results,
description,
datasource,
reseller_id
) values (
NOW(),
'ImportScript',
'localhost',
'%s',
'%s',
'Imported %s',
'%s',
%d
)",
addslashes($watermark),
addslashes($results),
addslashes($filename),
addslashes($dir),
addslashes($reseller)
);
$log = sprintf(
"Imported file %s, %d records have been affected\n",
$filename,
$results
);
syslog(LOG_NOTICE, $log);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
}
function showImportProgress($filename = 'unspecified', $increment = 5000)
{
$this->importIndex++;
if ($this->importIndex == $increment) {
printf("Loaded %d records from %s\n", $this->importIndex, $filename);
flush();
$this->importIndex=0;
}
}
function createRatingTable($name)
{
if ($name) {
$table='billing_rates_'.$name;
} else {
$table='billing_rates_default';
}
$query = sprintf(
"create table %s select * from billing_rates where name = '%s'\n",
addslashes($table),
addslashes($name)
);
if ($this->db->query($query)) {
$query = sprintf(
"alter table %s add index rate_idx (name)",
addslashes($table)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
$query = sprintf(
"alter table %s add index destination_idx (destination)",
addslashes($table)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
printf("Created table %s\n", $table);
return true;
} else {
return false;
}
}
public function splitRatingTable()
{
$query = "select count(*) as c from billing_rates";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$this->db->next_record();
$rows=$this->db->f('c');
$query="select distinct(name) from billing_rates order by name ASC";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
while ($this->db->next_record()) {
$rate_names[]=$this->db->f('name');
}
foreach ($rate_names as $name) {
if (!$name) $name='default';
$table="billing_rates_".$name;
$query = sprintf("drop table if exists %s", addslashes($table));
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$query = sprintf(
"create table %s select * from billing_rates where name = '%s'\n",
addslashes($table),
addslashes($name)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
} else {
$query = sprintf(
"alter table %s add index rate_idx (name)",
addslashes($table)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$query = sprintf(
"alter table %s add index destination_idx (destination)",
addslashes($table)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$query = sprintf("select count(*) as c from %s", addslashes($table));
$this->db->query($query);
$this->db->next_record();
$records=$this->db->f('c');
$created_records=$created_records+$records;
$progress=100*$created_records/$rows;
printf(
"Created table %s with %s records (%.1f %s)\n",
$table,
$records,
$progress,
'%'
);
}
}
return true;
}
public function updateTable()
{
global $auth;
$loginname=$auth->auth["uname"];
foreach ($this->web_elements as $_el) {
${$_el}= $_REQUEST[$_el];
}
if (!$table) return false;
if ($this->readonly) {
return true;
}
// Init table structure
if (!is_array($this->tables[$table]['exceptions'])) $this->tables[$table]['exceptions']=array();
if (!is_array($this->tables[$table]['keys'])) $this->tables[$table]['keys']=array();
if (!is_array($this->tables[$table]['fields'])) $this->tables[$table]['fields']=array();
$metadata = $this->db->metadata($table = "$table");
$cc = count($metadata);
// end init table structure
if ($web_task =="update") {
$affected_rows=0;
if ($subweb_task == "Update") {
if ($this->checkValues($table, $_REQUEST)) {
$update_set='';
$k=0;
while ($k < $cc) {
$k++;
$Fname=$metadata[$k]['name'];
if (!$Fname) continue;
$value=$_REQUEST[$Fname];
if ($this->tables[$table]['fields'][$Fname]['readonly']) {
continue;
}
if (in_array($Fname, $this->tables[$table]['exceptions'])) {
continue;
}
if (in_array($Fname, $this->tables[$table]['keys'])) {
continue;
}
if ($kkk > 0) {
$comma = ",";
} else {
$comma = "";
}
if (!$this->tables[$table]['skip_math'] && preg_match("/^([\+\-\*\/])(.*)$/", $value, $sign)) {
$update_set .= $comma.addslashes($Fname)."= ROUND(".addslashes($Fname). " ".$sign[1]. "'".$sign[2]."')";
} else {
$update_set .= $comma.addslashes($Fname)."='".addslashes($value)."'";
}
$kkk++;
}
$k=0;
while ($k < $cc) {
if ($metadata[$k]['name'] == 'change_date') {
$update_set .= sprintf("%s %s = NOW() ", $comma, addslashes($metadata[$k]['name']));
break;
}
$k++;
}
$log_entity=" id = $id ";
$where = sprintf(" id = '%s' and %s", addslashes($id), $this->whereResellerFilter);
if ($table == "billing_rates") {
if ($this->settings['split_rating_table']) {
$rate_table_affected = array();
$query_r = "select distinct (name) from billing_rates where". $where;
if ($this->db->query($query_r)) {
while ($this->db->next_record()) {
$rate_tables_affected[]='billing_rates_'.$this->db->f('name');
}
} else {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
}
} elseif ($table=="prepaid") {
register_shutdown_function("unLockTables", $this->db);
if ($this->db->query("lock table prepaid write")) {
$query_q = sprintf(
"select * from prepaid where account = '%s'",
addslashes($account)
);
if ($this->db->query($query_q) && $this->db->num_rows()) {
$this->db->next_record();
$old_balance=$this->db->f('balance');
}
$this->db->query("unlock tables");
}
}
$query = sprintf(
"update %s set %s where %s ",
addslashes($table),
$update_set,
$where
);
if ($this->db->query($query)) {
$affected_rows=$this->db->affected_rows();
if ($affected_rows) {
if ($table=="prepaid") {
list($username, $domain) = explode("@", $account);
$value=$balance-$old_balance;
if (floatval($balance) != floatval($old_balance)) {
$query = sprintf(
"insert into prepaid_history
(username,domain,action,description,value,balance,date,reseller_id)
values
('%s','%s','Set balance','Manual update','%s','%s',NOW(),%d)",
addslashes($username),
addslashes($domain),
addslashes($value),
addslashes($balance),
$this->CDRTool['filter']['reseller']
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
}
} elseif ($table=='billing_rates') {
if ($this->settings['split_rating_table']) {
foreach ($rate_tables_affected as $extra_rate_table) {
$query_u = sprintf(
"update %s set %s where %s ",
addslashes($extra_rate_table),
$update_set,
$where
);
if (!$this->db->query($query_u)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query_u,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
}
}
}
if (in_array($table, $this->requireReload)) {
if (!$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'")) {
printf(
"Database error: %s (%s)",
$this->db->Error,
$this->db->Errno
);
}
}
}
} else {
printf(
"Database error for query '%s': %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
}
} else {
print "Correct the values and try again.";
}
} elseif ($subweb_task == "Update selection") {
$k=0;
$kkk=0;
$update_set='';
while ($k < $cc) {
$k++;
$Fname=$metadata[$k]['name'];
$value=$_REQUEST[$Fname];
if (!strlen($value)) continue;
if ($this->tables[$table]['fields'][$Fname]['readonly']) {
continue;
}
if (in_array($Fname, $this->tables[$table]['exceptions'])) {
continue;
}
if (in_array($Fname, $this->tables[$table]['keys'])) {
continue;
}
if ($kkk > 0) {
$comma = ",";
} else {
$comma="";
}
if ($value == "NULL") {
$value="";
}
if (preg_match("/^([\+\-\*\/])(.*)$/", $value, $sign)) {
$update_set .= $comma.$Fname." = ROUND(".$Fname. " ".$sign[1]. "'".$sign[2]."')";
} else {
$update_set .= $comma.$Fname." = '".$value."'";
}
$kkk++;
}
$where = $this->whereResellerFilter;
if ($kkk) {
// reconstruct where clause to apply all changes to selection
// build where clause
// Search build for each field
$j=0;
while ($j < $cc) {
$Fname=$metadata[$j]['name'];
$size=$metadata[$j]['len'];
if (!in_array($Fname, $this->tables[$table]['exceptions'])) {
$f_name="search_".$Fname;
$value=$_REQUEST[$f_name];
if (preg_match("/^([<|>]+)(.*)$/", $value, $likes)) {
$like=$likes[1];
$likewhat=$likes[2];
$quotes="";
} else {
$like="like";
$likewhat=$value;
$quotes="'";
}
if (strlen($value)) {
$where .= " and $Fname $like $quotes".$likewhat."$quotes";
$t++;
}
}
$j++;
}
if ($table == 'billing_rates') {
if ($this->settings['split_rating_table']) {
$rate_table_affected = array();
$query_r = "select distinct (name) from billing_rates where". $where;
if ($this->db->query($query_r)) {
while ($this->db->next_record()) {
$rate_tables_affected[] = 'billing_rates_'.$this->db->f('name');
}
} else {
printf(
"Database error: %s (%s)",
$this->db->Error,
$this->db->Errno
);
}
}
}
$query = sprintf(
"update %s set %s where %s ",
addslashes($table),
$update_set,
$where
);
if ($this->db->query($query)) {
$affected_rows=$this->db->affected_rows();
if ($affected_rows) {
if ($table == 'billing_rates') {
if ($this->settings['split_rating_table']) {
foreach ($rate_tables_affected as $extra_rate_table) {
$query_u = sprintf(
"update %s set %s where %s ",
addslashes($extra_rate_table),
$update_set,
$where
);
if (!$this->db->query($query_u)) {
printf(
"Database error for %s: %s (%s)",
$query_u,
$this->db->Error,
$this->db->Errno
);
}
}
}
}
if (in_array($table, $this->requireReload)) {
$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'");
}
}
} else {
printf("Database error: %s", $this->db->Error);
}
}
} elseif ($subweb_task == "Delete selection") {
if ($confirmDelete) {
// reconstruct where clause to apply all changes to selection
// build where clause
// Search build for each field
$where = $this->whereResellerFilter;
$j=0;
while ($j < $cc) {
$Fname=$metadata[$j]['name'];
$size=$metadata[$j]['len'];
if (!in_array($Fname, $this->tables[$table]['exceptions'])) {
$f_name="search_".$Fname;
$value=$_REQUEST[$f_name];
if (preg_match("/^([<|>]+)(.*)$/", $value, $likes)) {
$like = $likes[1];
$likewhat = $likes[2];
$quotes = "";
} else {
$like = "like";
$likewhat = $value;
$quotes = "'";
}
if (strlen($value)) {
$where .= " and $Fname $like $quotes".$likewhat."$quotes";
$t++;
}
}
$j++;
}
if ($table == 'billing_rates') {
if ($this->settings['split_rating_table']) {
$rate_table_affected=array();
$query_r = "select distinct (name) from billing_rates where". $where;
if ($this->db->query($query_r)) {
while ($this->db->next_record()) {
$rate_tables_affected[] = 'billing_rates_'.$this->db->f('name');
}
} else {
printf(
"Database error: %s (%s)",
$this->db->Error,
$this->db->Errno
);
}
}
}
$query = sprintf(
"delete from %s where %s",
addslashes($table),
$where
);
if ($this->db->query($query)) {
$affected_rows = $this->db->affected_rows();
if ($affected_rows) {
if ($table == 'billing_rates') {
if ($this->settings['split_rating_table']) {
foreach ($rate_tables_affected as $extra_rate_table) {
$query_u = sprintf(
"delete from %s where %s ",
addslashes($extra_rate_table),
$where
);
if (!$this->db->query($query_u)) {
printf(
"Database error for %s: %s (%s)",
$query_u,
$this->db->Error,
$this->db->Errno
);
}
}
}
}
if (in_array($table, $this->requireReload)) {
$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'");
}
}
} else {
printf(
"Database error: %s",
$this->db->Error
);
}
unset($confirmDelete);
} else {
print "
";
print "Please confirm the deletion by pressing the Delete button again. ";
print "";
print "";
}
} elseif ($subweb_task == "Copy rate" && strlen($fromRate) && strlen($toRate)) {
$toRate=preg_replace("/%/", "", $toRate);
if ($confirmCopy) {
if ($toRate == 'history') {
$values = sprintf(
"(reseller_id,name,destination,application,connectCost,durationRate,connectCostIn,durationRateIn,startDate,endDate)
select
billing_rates.reseller_id,
'%s',
billing_rates.destination,
billing_rates.application,
billing_rates.connectCost,
billing_rates.durationRate,
billing_rates.connectCostIn,
billing_rates.durationRateIn,
NOW(),
NOW()
from billing_rates ",
addslashes($fromRate)
);
} else {
$values = sprintf(
"(reseller_id,name,destination,application,connectCost,durationRate,connectCostIn,durationRateIn)
select
billing_rates.reseller_id,
'%s',
billing_rates.destination,
billing_rates.application,
billing_rates.connectCost,
billing_rates.durationRate,
billing_rates.connectCostIn,
billing_rates.durationRateIn
from billing_rates ",
addslashes($toRate)
);
}
$where = $this->whereResellerFilter;
$j=0;
while ($j < $cc) {
$Fname=$metadata[$j]['name'];
$size=$metadata[$j]['len'];
if (!in_array($Fname, $this->tables[$table]['exceptions'])) {
$f_name="search_".$Fname;
$value=$_REQUEST[$f_name];
if (preg_match("/^([<|>]+)(.*)$/", $value, $likes)) {
$like=$likes[1];
$likewhat=$likes[2];
$quotes="";
} else {
$like="like";
$likewhat=$value;
$quotes="'";
}
if (strlen($value)) {
$where .= sprintf(
" and %s %s %s%s%s ",
addslashes($Fname),
$like,
$quotes,
addslashes($likewhat),
$quotes
);
$t++;
}
}
$j++;
}
if ($toRate == 'history') {
$query="insert into billing_rates_history $values where $where";
} else {
$query="insert into billing_rates $values where $where";
}
if ($this->db->query($query)) {
$affected_rows=$this->db->affected_rows();
if ($affected_rows) {
print "$affected_rows rates copied. ";
if ($table == 'billing_rates') {
if ($this->settings['split_rating_table']) {
$query = sprintf(
"create table billing_rates_%s select * from billing_rates where %s ",
addslashes($toRate),
$where
);
if (!$this->db->query($query)) {
printf(
"Database error for %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
}
}
}
if (in_array($table, $this->requireReload)) {
$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'");
}
}
if ($toRate == 'history') {
// Switch to history
$table = 'billing_rates_history';
// Init table structure
$this->tables[$table]['exceptions']= $this->tables[$table]['exceptions'];
$this->tables[$table]['keys'] = $this->tables[$table]['keys'];
$this->tables[$table]['fields'] = $this->tables[$table]['fields'];
$metadata = $this->db->metadata($table = "$table");
$cc = count($metadata);
// end init table structure
}
unset($confirmCopy);
} else {
printf("Database error: %s", $this->db->Error);
}
$log_entity="rate=$toRate";
} else {
print "
";
print "Please confirm the copy of rate $fromRate to $toRate. ";
print "";
}
} elseif ($subweb_task == "Insert") {
//print "
Insert
";
if ($this->checkValues($table, $_REQUEST)) {
$query=sprintf("insert into %s ( ", addslashes($table));
$k=1;
$kkk=0;
while ($k < $cc) {
$Fname=$metadata[$k]['name'];
if (!in_array($Fname, $this->tables[$table]['exceptions'])) {
if ($kkk > 0) {
$comma = ",";
} else {
$comma="";
}
$query .= $comma.addslashes($Fname);
$kkk++;
}
$k++;
}
$query .= ") values ( ";
$k=1;
$kkk=0;
while ($k < $cc) {
$Fname=$metadata[$k]['name'];
$value=$_REQUEST[$Fname];
if (!in_array($Fname, $this->tables[$table]['exceptions'])) {
if ($kkk > 0) {
$comma = ",";
} else {
$comma="";
}
if ($Fname == 'reseller_id' && $this->CDRTool['filter']['reseller']) {
$query .= $comma."'".addslashes($this->CDRTool['filter']['reseller'])."'";
} else {
$query .= $comma."'".addslashes($value)."'";
}
$kkk++;
}
$k++;
}
$query .= ") ";
$k=1;
while ($k < $cc) {
$Fname=$metadata[$k]['name'];
$value=$_REQUEST[$Fname];
if (in_array($Fname, $this->tables[$table]['keys'])) {
if ($value == "") {
$Fname_print_insert = substr($Fname, 4);
print "$Fname_print_insert = ????
";
$empty_insert = 1;
}
}
$k++;
}
if (!$empty_insert) {
if ($this->db->query($query)) {
$affected_rows=$this->db->affected_rows();
if ($affected_rows) {
$this->db->query("select LAST_INSERT_ID() as lid");
$this->db->next_record();
$log_entity = sprintf("id=%s", $this->db->f('lid'));
if (in_array($table, $this->requireReload)) {
$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'");
}
}
} else {
printf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
}
} else {
print "
Error: The insert statement contains an empty key!
";
}
} else {
print "Correct the values and try again.";
}
} elseif ($subweb_task == "Delete") {
if ($confirmDelete) {
$query = sprintf(
"delete from %s where id = '%s' and %s ",
addslashes($table),
addslashes($id),
addslashes($this->whereResellerFilter)
);
if ($this->db->query($query)) {
$affected_rows=$this->db->affected_rows();
if ($affected_rows && in_array($table, $this->requireReload)) {
$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'");
}
$log_entity = sprintf("id=%s", $id);
} else {
printf("Database error: %s", $this->db->Error);
}
unset($confirmDelete);
} else {
$idForDeletion=$id;
print "
";
print "Please confirm the deletion by pressing the Delete button again. ";
print "";
print "";
}
} elseif ($subweb_task == "Delete session" && $sessionId && $table=='prepaid') {
$query = sprintf(
"select active_sessions from %s where id = %d and %s",
addslashes($table),
addslashes($id),
$this->whereResellerFilter
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
}
if (!$this->db->num_rows()) return;
$this->db->next_record();
if (strlen($this->db->f('active_sessions'))) {
// remove session
$active_sessions=array();
$old_active_sessions = json_decode($this->db->f('active_sessions'), true);
if (!count($old_active_sessions)) return;
foreach (array_keys($old_active_sessions) as $_key) {
if ($_key==$sessionId) continue;
$active_sessions[$_key]=$old_active_sessions[$_key];
}
} else {
$active_sessions=array();
}
$query = sprintf(
"update %s
set active_sessions = '%s',
session_counter = %d
where id = %d",
addslashes($table),
addslashes(json_encode($active_sessions)),
count($active_sessions),
addslashes($id)
);
if ($this->db->query($query)) {
return 1;
} else {
$log = sprintf(
"Database error for %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
print $log;
return 0;
}
}
if ($affected_rows && $table!="prepaid") {
$log_query = sprintf(
"insert into log
(date,login,ip,datasource,results,description,reseller_id)
values (NOW(),'%s','%s','Rating','%d','%s in table %s %s',%d)",
addslashes($loginname),
addslashes($_SERVER['REMOTE_ADDR']),
addslashes($affected_rows),
addslashes($subweb_task),
addslashes($table),
addslashes($log_entity),
addslashes($this->CDRTool['filter']['reseller'])
);
$this->db->query($log_query);
}
}
}
public function showTable()
{
$PHP_SELF=$_SERVER['PHP_SELF'];
foreach ($this->web_elements as $_el) {
${$_el}= $_REQUEST[$_el];
}
if ($this->table == 'prepaid_cards') {
print "
Prepaid card generator";
}
// Init table structure
if (!is_array($this->tables[$this->table]['exceptions'])) $this->tables[$this->table]['exceptions']=array();
if (!is_array($this->tables[$this->table]['keys'])) $this->tables[$this->table]['keys']=array();
if (!is_array($this->tables[$this->table]['fields'])) $this->tables[$this->table]['fields']=array();
if ($this->table=='prepaid' && strlen($_REQUEST['search_session_counter'])) {
$this->readonly=true;
}
if ($this->readonly) {
$this->tables[$this->table]['readonly']=1;
}
$metadata = $this->db->metadata($this->table);
$cc = count($metadata);
// end init table structure
//
// delimiter for exporting records
if ($this->settings['csv_delimiter']) {
$delimiter=$this->settings['csv_delimiter'];
} else {
$delimiter=",";
}
$query = sprintf(
"select count(*) as c from %s where %s",
addslashes($this->table),
$this->whereResellerFilter
);
$t=0;
$j=0;
while ($j < $cc) {
$Fname=$metadata[$j]['name'];
$size=$metadata[$j]['len'];
$class=$metadata[$j]['class'];
if (!in_array($Fname, $this->tables[$this->table]['exceptions'])) {
$f_name="search_".$Fname;
$value=$_REQUEST[$f_name];
if (preg_match("/^([<|>]+)(.*)$/", $value, $likes)) {
$like=$likes[1];
$likewhat=$likes[2];
$quotes="";
} else {
$like="like";
$likewhat=$value;
$quotes="'";
}
if (strlen($value)) {
$where .= sprintf(
" and %s %s %s%s%s ",
addslashes($Fname),
$like,
$quotes,
addslashes($likewhat),
$quotes
);
$t++;
}
}
$j++;
}
$query .= $where;
$this->db->query($query);
$this->db->next_record();
$rows=$this->db->Record['c'];
if (!$export) {
print "
|
";
if ($rows == 0) {
print "No records found. ";
} else {
print "$selectie $rows records found. ";
}
if ($this->settings['socketIPforClients'] && $this->settings['socketPort']) {
$engineAddress = $this->settings['socketIPforClients'].":".$this->settings['socketPort'];
if ($this->checkRatingEngineConnection()) {
print " | Rating engine running at $engineAddress";
} else {
print " | Cannot connect to rating engine $engineAddress";
}
}
print " | Rating documentation";
print "
|
";
if ($this->csv_import[$this->table]) {
print "
|
";
} else {
print " | ";
}
print " |
";
} else {
$this->maxrowsperpage = 10000000;
}
if (!$next) {
$i=0;
$next=0;
} else {
$i=intval($next);
}
$j=0;
$z=0;
if ($rows > $this->maxrowsperpage) {
$maxrows = $this->maxrowsperpage + $next;
if ($maxrows > $rows) {
$maxrows=$rows;
$prev_rows=$maxrows;
}
} else {
$maxrows=$rows;
}
if (!$order && $this->tables[$this->table]['order']) {
$order = sprintf(
" order by %s ",
addslashes($this->tables[$this->table]['order'])
);
}
$query = sprintf(
"select * from %s where (1=1) %s and %s %s limit %d, %d",
addslashes($this->table),
$where,
$this->whereResellerFilter,
$order,
intval($i),
intval($this->maxrowsperpage)
);
$this->db->query($query);
$num_fields=$this->db->num_fields();
$k=0;
if (!$export) {
if ($this->table=='prepaid') {
print "
| ";
} else {
print "
|
";
}
}
while ($k < $cc) {
$th = $metadata[$k]['name'];
if (!in_array($th, $this->tables[$this->table]['exceptions'])) {
if ($this->tables[$this->table]['fields'][$th]['name']) {
$th=$this->tables[$this->table]['fields'][$th]['name'];
} else {
$th=ucfirst($th);
}
if (!$export) {
print "$th | ";
} else {
if ($k) {
printf("%s%s", $delimiter, $th);
} else {
print "Ops";
}
}
$t_columns++;
}
$k++;
}
if ($export) {
print "\n";
}
if (!$export) {
print "
Action |
";
$t_columns=$t_columns+2;
// SEARCH FORM
print "
|
Use _ to match one character and % to match any. Use > or <
to find greater or smaller values. |
";
// Search form
print "
";
//print "
//
//
|
//
//";
if ($selection_made && !$this->tables[$this->table]['readonly']) {
// Update all form
print "
|
Use + or - to add/substract from curent values.
Use * or / to multiply/divide curent values. |
";
$j=0;
print "
";
} elseif (!$this->tables[$this->table]['readonly']) {
// Insert form
$j=0;
print "
";
//print "
//
//
|
//
//";
}
}
while ($i < $maxrows) {
$this->db->next_record();
$id = $this->db->f('id');
$status = $this->db->f('status');
$found = $i+1;
if (!$export) {
print "
";
if ($extraInfo!='') {
print "
|
$extraInfo |
";
}
} else {
if ($this->table == 'prepaid') {
print "
|
$extraInfo |
";
}
}
}
$i++;
}
if (!$export) {
print "
";
print "
";
print "